8.4 Subquery Cardinality & NULL Traps

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)                           |
|                                                                         |
+-------------------------------------------------------------------------+

---

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.

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;