ExamFinch.com My Study

Study guide

8.4 Subquery Cardinality & NULL Traps

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

8.4 Subquery Cardinality & NULL Traps

Key Takeaways Explain This Topic AI

If a subquery evaluated by NOT IN returns ANY NULL value, the entire condition evaluates to UNKNOWN for all rows, causing the outer query to return ZERO rows.

In SQL three-valued logic, 'v NOT IN (10, 20, NULL)' expands to 'v <> 10 AND v <> 20 AND v <> NULL'; because 'v <> NULL' is UNKNOWN, the entire conjunction cannot be TRUE.

NOT EXISTS is completely immune to the NULL trap because it tests for row existence (cardinality) rather than performing column value equality comparisons.

When an un-correlated subquery returns zero rows, single-row operators evaluate to NULL, IN evaluates to FALSE, and ALL evaluates to TRUE (vacuously true).

To defend against NULL traps, developers must either filter NULLs in subqueries using 'WHERE column IS NOT NULL' or refactor queries to use NOT EXISTS.

Last updated: August 2026

8.4 Subquery Cardinality & NULL Traps

One of the most heavily tested and conceptually nuanced areas on the Oracle 1Z0-071 examination is the interaction between subqueries , result-set cardinality , and three-valued boolean logic ( TRUE , FALSE , UNKNOWN ) . A single unexpected NULL value in a subquery result set can silently alter query behavior—turning a query that should return hundreds of rows into one that returns zero rows without throwing any runtime errors.

Three-Valued Logic in SQL Comparisons

In standard boolean logic, an expression is either TRUE or FALSE . In SQL, however, NULL represents missing, unassigned, or unknown information. When any value is compared to NULL using standard relational operators ( = , <> , > , < , <= , >= ), the result is UNKNOWN . +-------------------------------------------------------------------------+

  • | THREE-VALUED LOGIC TRUTH TABLES |
  • +-------------------------------------------------------------------------+
  • | |
  • | AND Operation: OR Operation: |
  • | +---------+---------+---------+ +---------+---------+---------+ |
  • | | AND | TRUE | UNKNOWN | | OR | TRUE | UNKNOWN | |
  • | +---------+---------+---------+ +---------+---------+---------+ |
  • | | TRUE | TRUE | UNKNOWN | | TRUE | TRUE | TRUE | |
  • | | FALSE | FALSE | FALSE | | FALSE | UNKNOWN | UNKNOWN | |
  • | | UNKNOWN | UNKNOWN | UNKNOWN | | UNKNOWN | TRUE | UNKNOWN | |
  • | +---------+---------+---------+ +---------+---------+---------+ |
  • | |
  • | Rule for WHERE and HAVING Clauses: |
  • | A row is retained in the result set ONLY if the predicate is TRUE. |
  • | If the predicate evaluates to FALSE or UNKNOWN, the row is DISCARDED. |
  • +-------------------------------------------------------------------------+
  • The Classic 1Z0-071 NULL Trap with NOT IN
  • Consider the classic business question: "Find all departments that currently have no employees."

A developer might intuitively write the following query using NOT IN : -- DANGEROUS QUERY: Highly vulnerable to the NOT IN NULL trap!

  • SELECT department_id, department_name
  • FROM departments
  • WHERE department_id NOT IN (
  • SELECT department_id
  • FROM employees
  • );
  • The Failure Scenario

Suppose the EMPLOYEES table contains 107 rows. One employee (e.g., Kimberly Grant) has not yet been assigned to a department, so her DEPARTMENT_ID is NULL . The subquery returns the list: { 10, 20, 30, ..., 110, NULL } .

Let us trace how Oracle evaluates the WHERE clause for Department 190 (which has no employees): 1. Expression to evaluate:

190 NOT IN (10, 20, 30, ..., NULL)

2. SQL expands NOT IN into a series of AND NOT EQUAL (<>) conditions:

(190 <> 10) AND (190 <> 20) AND ... AND (190 <> 110) AND (190 <> NULL)

3. Evaluate each component:

  • (190 <> 10) --> TRUE
  • (190 <> 20) --> TRUE

- ...

  • (190 <> 110) --> TRUE
  • (190 <> NULL) --> UNKNOWN

4. Combine with AND:

TRUE AND TRUE AND ... AND TRUE AND UNKNOWN

===> UNKNOWN !

Because the final condition evaluates to UNKNOWN , Department 190 is discarded . This exact same evaluation occurs for every single department in the table.

The Trap Revealed: If a subquery used with NOT IN returns even one NULL value , the entire outer query returns ZERO rows , regardless of whether unassigned records exist!

Why IN Is NOT Vulnerable to the Same Trap

Why does IN succeed where NOT IN fails? Let us examine the boolean expansion of IN with a NULL present: -- Finding employees who work in departments 10, 20, or NULL:

WHERE department_id IN (10, 20, NULL)

-- Expands to OR EQUAL (=) conditions:

(department_id = 10) OR (department_id = 20) OR (department_id = NULL)

If an employee has department_id = 10 : TRUE OR FALSE OR UNKNOWN ⟹ \implies ⟹ TRUE (Row returned!).

If an employee has department_id = 50 : FALSE OR FALSE OR UNKNOWN ⟹ \implies ⟹ UNKNOWN (Row discarded).

Because OR evaluates to TRUE as long as any single operand is TRUE , IN works properly for matching rows even when NULL is part of the subquery list.

NOT IN vs. NOT EXISTS Under NULL Conditions

The NOT EXISTS operator avoids value-level equality comparisons completely. It checks only whether the subquery produces at least one row . -- SAFE AND ROBUST QUERY: Works perfectly even with NULLs in employees table

  • SELECT d.department_id, d.department_name
  • FROM departments d
  • WHERE NOT EXISTS (
  • SELECT 1
  • FROM employees e
  • WHERE e.department_id = d.department_id
  • ); +-------------------------------------------------------------------------+
  • | NOT IN vs. NOT EXISTS UNDER NULL VALUES |
  • +-------------------------------------------------------------------------+
  • | |
  • | Scenario: EMPLOYEES table contains rows with DEPARTMENT_ID = NULL |
  • | |
  • | Candidate: Department 190 (No employees assigned) |
  • | |
  • | 1. NOT IN Evaluation: |
  • | 190 NOT IN (10, 20, ..., NULL) |
  • | --> Expands with <> AND ... <> NULL |
  • | --> Evaluates to UNKNOWN |
  • | --> Dept 190 is OMITTED (WRONG RESULT) |
  • | |
  • | 2. NOT EXISTS Evaluation: |
  • | NOT EXISTS (SELECT 1 FROM emp WHERE department_id = 190) |
  • | --> Subquery searches for rows where department_id = 190 |
  • | --> Finds 0 matching rows |
  • | --> NOT EXISTS(0 rows) evaluates to TRUE |
  • | --> Dept 190 is RETURNED (CORRECT RESULT) |
  • | |
  • +-------------------------------------------------------------------------+

Operator Subquery Returns NULL Outer Query Behavior Exam Recommendation

IN Yes Returns rows matching non-null values. Safe for positive matching.

NOT IN Yes Returns 0 rows (silent failure). Dangerous! Avoid or add IS NOT NULL .

EXISTS Yes Returns rows where correlated match exists. Safe & fast.

NOT EXISTS Yes Returns correct non-matching rows. Preferred standard for anti-joins.

Subquery Cardinality & Operator Behavior Matrix

Understanding how different subquery comparison operators behave when the subquery returns zero rows , one row , or multiple rows is critical for the exam.

Subquery Return Cardinality Operator: = (Single-Row) Operator: IN Operator: NOT IN (no nulls) Operator: > ANY Operator: > ALL Operator: EXISTS Operator: NOT EXISTS

  • 0 Rows NULL (Unknown) FALSE TRUE FALSE TRUE (vacuously) FALSE TRUE
  • 1 Row (e.g., 5000) Compares to 5000 Matches 5000 Checks != 5000 > 5000 > 5000 TRUE FALSE
  • Multiple Rows (no nulls) ORA-01427 Matches any Matches none > MIN(vals) > MAX(vals) TRUE FALSE
  • Multiple Rows (with NULL) ORA-01427 Matches non-nulls UNKNOWN (0 rows) > MIN(non-null) UNKNOWN TRUE FALSE

The > ALL Zero-Row Paradox: If a subquery returns zero rows , > ALL (empty set) evaluates to TRUE ! In mathematical set theory, a condition asserted over all elements of an empty set is vacuously true (no element exists in the set to violate the condition).

Defensive SQL Coding Patterns

To ensure your queries never fall victim to unexpected NULLs on production systems or exam questions, follow these two defensive coding patterns:

Pattern 1: Explicitly Exclude NULLs in Subqueries

If you must use NOT IN , always include a WHERE column IS NOT NULL clause inside the subquery: -- DEFENSIVE NOT IN PATTERN:

  • SELECT department_id, department_name
  • FROM departments
  • WHERE department_id NOT IN (
  • SELECT department_id
  • FROM employees
  • WHERE department_id IS NOT NULL
  • );
  • Pattern 2: Use LEFT OUTER JOIN with IS NULL Filter

An alternative ANSI SQL standard pattern for anti-joins is joining the parent to the child and filtering for unmatched foreign keys: -- ANSI OUTER JOIN ANTI-JOIN PATTERN:

  • SELECT d.department_id, d.department_name
  • FROM departments d
  • LEFT OUTER JOIN employees e
  • ON d.department_id = e.department_id
  • WHERE e.department_id IS NULL;