Database Management SystemsSQL Joins & Subqueries

Correlated Subqueries

LevelIntermediate

Duration60 mins

TopicSQL Joins & Subqueries

3 / 5

NOT EXISTS

Finding What Isn't There

Consider these questions: "Which customers have never placed an order?" "Which products have no reviews?" "Which employees have no direct reports?"

Each asks about the absence of related data—a fundamentally different question than existence. Finding what exists is straightforward; finding what doesn't exist requires special handling.

The NOT EXISTS operator is SQL's primary tool for answering absence questions. It performs what's called an anti-join—returning outer rows that have no matching inner rows. This pattern is essential for data quality auditing, gap detection, cleanup operations, and many business reporting scenarios.

What You Will Learn

By the end of this page, you will understand NOT EXISTS semantics, master its relationship with anti-joins, learn multiple approaches to finding absent data (NOT EXISTS, NOT IN, LEFT JOIN + NULL), and understand when each approach is appropriate.

NOT EXISTS Fundamentals: Inverse Existence Testing

NOT EXISTS is the logical negation of EXISTS. It returns TRUE when the subquery returns zero rows, and FALSE when the subquery returns one or more rows.

The NOT EXISTS Semantics:

NOT EXISTS (subquery) returns:
  TRUE  → if subquery produces 0 rows (nothing exists)
  FALSE → if subquery produces 1 or more rows

This inverted logic makes NOT EXISTS perfect for finding:

Records without related records (orphans)
Gaps in sequences or relationships
Exclusion conditions ("all X without Y")
Set difference operations

not_exists_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Basic NOT EXISTS syntax
SELECT columns
FROM outer_table
WHERE NOT EXISTS (
    SELECT 1
    FROM inner_table
    WHERE correlation_condition
);
 
-- Concrete example: Find customers WITHOUT orders
SELECT c.customer_id, c.name, c.email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id  -- Correlation
);
 
-- This reads as: "For each customer, check if NO orders exist
-- where the order's customer_id matches this customer.
-- If no orders exist, include this customer in results."

Mental Model: "Prove Me Wrong"

Think of NOT EXISTS as saying 'I claim there are no related rows. Try to prove me wrong.' If the subquery finds even one row, NOT EXISTS returns FALSE. If the subquery exhaustively searches and finds nothing, NOT EXISTS returns TRUE.

Common NOT EXISTS Patterns

NOT EXISTS appears in several recurring patterns across different domains. Recognizing these patterns helps you apply NOT EXISTS effectively to new problems.

Finding Records Without Related Data:

The most common NOT EXISTS use case—finding 'orphan' records.

orphan_detection.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Customers who have never ordered
SELECT c.customer_id, c.name, c.signup_date
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
 
-- Products with no inventory records
SELECT p.product_id, p.name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM inventory i WHERE i.product_id = p.product_id
);
 
-- Employees with no time entries (potential data quality issue)
SELECT e.employee_id, e.name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM time_entries t 
    WHERE t.employee_id = e.employee_id
    AND t.entry_date >= CURRENT_DATE - 30
);

NOT EXISTS vs NOT IN: The Critical Difference

Both NOT EXISTS and NOT IN can express "find rows without matches," but they have critically different NULL behavior. This difference causes subtle bugs that are difficult to diagnose.

The Core Issue: NOT IN with NULLs in the subquery can return no rows at all—even when it logically should return results.

not_in_null_problem.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Setup: orders table contains customer_ids including NULLs
-- customers: (1, 'Alice'), (2, 'Bob'), (3, 'Carol')
-- orders: customer_id values are (1, 2, NULL)
 
-- NOT IN approach (DANGEROUS with NULLs):
SELECT c.name
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT customer_id FROM orders  -- Returns (1, 2, NULL)
);
 
-- Expected result: Carol (customer_id = 3)
-- Actual result: NO ROWS RETURNED!
 
-- Why? The NOT IN evaluates as:
-- 3 NOT IN (1, 2, NULL)
-- = NOT (3=1 OR 3=2 OR 3=NULL)
-- = NOT (FALSE OR FALSE OR UNKNOWN)
-- = NOT (UNKNOWN)
-- = UNKNOWN  ← Not TRUE, so row is excluded!
 
-- NOT EXISTS approach (SAFE with NULLs):
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);
 
-- For Carol (customer_id = 3):
-- Subquery: WHERE o.customer_id = 3
-- No rows match (NULL = 3 evaluates to UNKNOWN, not TRUE)
-- NOT EXISTS returns TRUE ✓
-- Carol is correctly returned!

NOT IN with NULLs is Dangerous

If the NOT IN subquery can ever return NULL values, the entire WHERE clause may evaluate to UNKNOWN for ALL rows, returning an empty result. This is counterintuitive and causes hard-to-debug issues. Always prefer NOT EXISTS unless you're certain NULLs cannot appear.

NOT EXISTS vs NOT IN Comparison
Aspect	NOT EXISTS	NOT IN
NULL in subquery	Handled correctly	Causes empty results
NULL in outer value	Works correctly	May cause issues
Semantics	Check for row absence	Value set membership
Clarity of intent	Clear (anti-join)	Can be ambiguous
Performance	Optimizer-friendly	May be equivalent
Recommendation	✓ Prefer in most cases	Use with caution

safe_not_in.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- If you must use NOT IN, explicitly exclude NULLs:
SELECT c.name
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT customer_id 
    FROM orders 
    WHERE customer_id IS NOT NULL  -- Explicit NULL exclusion
);
 
-- Or use COALESCE to handle NULLs:
SELECT c.name  
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT COALESCE(customer_id, -1) FROM orders  -- Convert NULL to sentinel
);
 
-- But NOT EXISTS is still cleaner:
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

NOT EXISTS vs LEFT JOIN + IS NULL

A classic alternative to NOT EXISTS is LEFT JOIN with a NULL check. Both are called anti-join patterns because they find rows without matches. They're logically equivalent but have different syntax and sometimes different performance characteristics.

NOT EXISTS Approach:

not_exists_anti.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Customers without orders
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
 
-- Characteristics:
-- ✓ Clear intent (absence check)
-- ✓ Subquery is encapsulated
-- ✓ Can include complex
--   conditions in subquery
-- ✓ Works with any correlation

LEFT JOIN + IS NULL Approach:

left_join_anti.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Customers without orders  
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o 
    ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;
 
-- Characteristics:
-- ✓ Familiar JOIN syntax
-- ✓ Can access joined columns
--   (though they'll be NULL)
-- ✗ Must check for NULL on
--   NOT-NULLable join column
-- ✗ Less explicit intent

Performance Considerations:

In modern databases, the optimizer typically transforms these into the same internal execution plan (an anti-join). However, differences can emerge in specific scenarios:

anti_join_complex.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- COMPLEX CONDITION: NOT EXISTS handles complex conditions elegantly
-- Find customers with no high-value orders in the last year
 
-- NOT EXISTS (cleaner for complex conditions):
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.total > 1000
    AND o.order_date >= CURRENT_DATE - INTERVAL '1 year'
);
 
-- LEFT JOIN (requires all conditions in ON clause):
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o 
    ON o.customer_id = c.customer_id
    AND o.total > 1000
    AND o.order_date >= CURRENT_DATE - INTERVAL '1 year'
WHERE o.order_id IS NULL;
 
-- Note: Conditions MUST be in ON, not WHERE!
-- This is WRONG (filters after join):
-- LEFT JOIN orders o ON o.customer_id = c.customer_id
-- WHERE o.order_id IS NULL AND o.total > 1000  -- Won't work as intended

LEFT JOIN Anti-Join Pitfall

When using LEFT JOIN for anti-join, ALL filter conditions must go in the ON clause, not the WHERE clause. Putting conditions in WHERE filters AFTER the join, which defeats the anti-join pattern. This is a common source of bugs.

When to Use Each

•NOT EXISTS — Prefer when the intent is clearly 'find rows without matches'. Cleaner for complex correlation conditions. More explicit about anti-join semantics.
•LEFT JOIN + IS NULL — Use when you need to access columns from the potentially-matching table (even though they'll be NULL), or when the query naturally fits the JOIN pattern.

Combining NOT EXISTS Conditions

Complex business rules often require combining multiple NOT EXISTS conditions, or mixing NOT EXISTS with EXISTS. Understanding how these combine is essential for expressing sophisticated logic.

multiple_not_exists.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- AND with multiple NOT EXISTS
-- Find customers who have never ordered AND never left reviews
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
AND NOT EXISTS (
    SELECT 1 FROM reviews r WHERE r.customer_id = c.customer_id
);
 
-- OR with NOT EXISTS
-- Find products missing inventory OR missing price  
SELECT p.product_id, p.name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM inventory i WHERE i.product_id = p.product_id
)
OR NOT EXISTS (
    SELECT 1 FROM pricing pr WHERE pr.product_id = p.product_id
);
 
-- Mixed EXISTS and NOT EXISTS
-- Find active customers who have ordered but never returned anything
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
AND NOT EXISTS (
    SELECT 1 FROM returns r WHERE r.customer_id = c.customer_id
);
 
-- De Morgan's Law application:
-- NOT (EXISTS A OR EXISTS B) = NOT EXISTS A AND NOT EXISTS B  
-- "Not (has orders OR has reviews)" = "Doesn't have orders AND doesn't have reviews"

De Morgan's Laws in SQL

Boolean algebra's De Morgan's Laws apply: NOT (A OR B) = (NOT A) AND (NOT B), and NOT (A AND B) = (NOT A) OR (NOT B). This helps when refactoring complex EXISTS/NOT EXISTS combinations.

NOT EXISTS for Data Quality Auditing

NOT EXISTS is invaluable for data quality auditing—finding referential integrity violations, orphaned records, and missing required relationships. These queries identify data problems before they cause application failures.

data_quality_audits.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- AUDIT 1: Orphaned foreign keys
-- Find orders referencing non-existent customers
SELECT o.order_id, o.customer_id
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id
);
 
-- AUDIT 2: Missing required relationships
-- Find employees without department assignment
SELECT e.employee_id, e.name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM department_assignments da
    WHERE da.employee_id = e.employee_id
    AND da.end_date IS NULL  -- Current assignment
);
 
-- AUDIT 3: Incomplete records
-- Find products missing required attributes
SELECT p.product_id, p.name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM product_attributes pa
    WHERE pa.product_id = p.product_id
    AND pa.attribute_type = 'WEIGHT'
)
OR NOT EXISTS (
    SELECT 1 FROM product_attributes pa
    WHERE pa.product_id = p.product_id
    AND pa.attribute_type = 'DIMENSIONS'
);
 
-- AUDIT 4: Stale or abandoned records
-- Find carts not converted to orders within 24 hours
SELECT c.cart_id, c.created_at, c.customer_id
FROM shopping_carts c
WHERE c.created_at < CURRENT_TIMESTAMP - INTERVAL '24 hours'
AND NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.source_cart_id = c.cart_id
);
 
-- AUDIT 5: Audit trail completeness
-- Find transactions without audit log entries
SELECT t.transaction_id, t.amount, t.created_at
FROM transactions t
WHERE NOT EXISTS (
    SELECT 1 FROM audit_log al
    WHERE al.entity_type = 'TRANSACTION'
    AND al.entity_id = t.transaction_id::text
);

Automated Data Quality Checks

These NOT EXISTS queries make excellent automated data quality checks. Schedule them to run nightly and alert when results are non-empty. Catching orphaned records early prevents cascading data corruption and application errors.

Optimizing NOT EXISTS Performance

While modern optimizers handle NOT EXISTS efficiently, certain patterns and indexing strategies ensure optimal performance, especially with large datasets.

Performance Best Practices

•Index the correlation columns — The correlation predicate (WHERE inner.fk = outer.pk) should use indexed columns. Without indexes, NOT EXISTS may require full table scans of the inner table for each outer row.
•Use SELECT 1 — While optimizers ignore the SELECT list, SELECT 1 is conventional and signals intent. Avoid SELECT * or complex expressions.
•Minimize subquery complexity — Keep the NOT EXISTS subquery focused. Complex JOINs or calculations within the subquery may prevent optimization.
•Consider LEFT JOIN alternative — Run EXPLAIN on both NOT EXISTS and LEFT JOIN versions. Sometimes one has a better plan for your specific data distribution.
•Filter outer query first — If possible, reduce the outer query result set before applying NOT EXISTS. This reduces the number of subquery executions.

not_exists_optimization.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- INDEX REQUIREMENT:
-- For this query to be efficient:
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- You need: CREATE INDEX idx_orders_customer_id ON orders(customer_id);
 
-- FILTER FIRST PATTERN:
-- Less efficient (NOT EXISTS checked for all customers):
SELECT c.customer_id, c.name
FROM customers c
WHERE c.signup_date >= '2024-01-01'
AND NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
 
-- More efficient (CTE filters first, though optimizer may do this anyway):
WITH recent_customers AS (
    SELECT customer_id, name
    FROM customers
    WHERE signup_date >= '2024-01-01'
)
SELECT rc.customer_id, rc.name
FROM recent_customers rc
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = rc.customer_id
);
 
-- EXAMINE EXECUTION PLAN:
EXPLAIN ANALYZE
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Look for: "Anti Join" or "Anti Semi Join" operator
-- Good sign that optimizer recognized the anti-join pattern

Summary: NOT EXISTS

We've thoroughly explored NOT EXISTS and anti-join patterns. Let's consolidate the key knowledge:

Key Takeaways

•NOT EXISTS finds absence — It returns TRUE when the subquery produces zero rows, enabling queries for orphans, gaps, and missing relationships.
•Prefer NOT EXISTS over NOT IN — NOT IN has dangerous NULL behavior that can return empty results. NOT EXISTS handles NULLs correctly.
•Two anti-join patterns — NOT EXISTS and LEFT JOIN + IS NULL are logically equivalent. NOT EXISTS is usually clearer; LEFT JOIN is sometimes more familiar.
•Division operation — Double NOT EXISTS (NOT EXISTS ... NOT EXISTS) implements relational division—finding rows related to ALL members of a set.
•Essential for data quality — NOT EXISTS queries find orphaned records, missing relationships, and integrity violations. Schedule them as automated checks.
•Index correlation columns — Performance depends on efficiently checking for non-matching rows. Indexes on correlation columns are mandatory for large tables.

Coming up next: We'll compare correlated vs non-correlated subqueries comprehensively, understanding when to use each, how to recognize opportunities for transformation, and the semantic differences that determine correctness.

Anti-Join Mastery

You now understand NOT EXISTS and anti-join patterns deeply—when to use them, how they handle NULLs, and why they're essential for data quality. Combined with EXISTS from the previous page, you have complete command over existence testing in SQL.

3 / 5

Loading learning content...

Database Management SystemsSQL Joins & Subqueries

Correlated Subqueries

LevelIntermediate

Duration60 mins

TopicSQL Joins & Subqueries

3 / 5

NOT EXISTS

Finding What Isn't There

Consider these questions: "Which customers have never placed an order?" "Which products have no reviews?" "Which employees have no direct reports?"

Each asks about the absence of related data—a fundamentally different question than existence. Finding what exists is straightforward; finding what doesn't exist requires special handling.

What You Will Learn

NOT EXISTS Fundamentals: Inverse Existence Testing

NOT EXISTS is the logical negation of EXISTS. It returns TRUE when the subquery returns zero rows, and FALSE when the subquery returns one or more rows.

The NOT EXISTS Semantics:

NOT EXISTS (subquery) returns:
  TRUE  → if subquery produces 0 rows (nothing exists)
  FALSE → if subquery produces 1 or more rows

This inverted logic makes NOT EXISTS perfect for finding:

Records without related records (orphans)
Gaps in sequences or relationships
Exclusion conditions ("all X without Y")
Set difference operations

not_exists_basic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Basic NOT EXISTS syntax
SELECT columns
FROM outer_table
WHERE NOT EXISTS (
    SELECT 1
    FROM inner_table
    WHERE correlation_condition
);
 
-- Concrete example: Find customers WITHOUT orders
SELECT c.customer_id, c.name, c.email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id  -- Correlation
);
 
-- This reads as: "For each customer, check if NO orders exist
-- where the order's customer_id matches this customer.
-- If no orders exist, include this customer in results."

Mental Model: "Prove Me Wrong"

Common NOT EXISTS Patterns

NOT EXISTS appears in several recurring patterns across different domains. Recognizing these patterns helps you apply NOT EXISTS effectively to new problems.

Finding Records Without Related Data:

The most common NOT EXISTS use case—finding 'orphan' records.

orphan_detection.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Customers who have never ordered
SELECT c.customer_id, c.name, c.signup_date
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
 
-- Products with no inventory records
SELECT p.product_id, p.name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM inventory i WHERE i.product_id = p.product_id
);
 
-- Employees with no time entries (potential data quality issue)
SELECT e.employee_id, e.name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM time_entries t 
    WHERE t.employee_id = e.employee_id
    AND t.entry_date >= CURRENT_DATE - 30
);

NOT EXISTS vs NOT IN: The Critical Difference

Both NOT EXISTS and NOT IN can express "find rows without matches," but they have critically different NULL behavior. This difference causes subtle bugs that are difficult to diagnose.

The Core Issue: NOT IN with NULLs in the subquery can return no rows at all—even when it logically should return results.

not_in_null_problem.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Setup: orders table contains customer_ids including NULLs
-- customers: (1, 'Alice'), (2, 'Bob'), (3, 'Carol')
-- orders: customer_id values are (1, 2, NULL)
 
-- NOT IN approach (DANGEROUS with NULLs):
SELECT c.name
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT customer_id FROM orders  -- Returns (1, 2, NULL)
);
 
-- Expected result: Carol (customer_id = 3)
-- Actual result: NO ROWS RETURNED!
 
-- Why? The NOT IN evaluates as:
-- 3 NOT IN (1, 2, NULL)
-- = NOT (3=1 OR 3=2 OR 3=NULL)
-- = NOT (FALSE OR FALSE OR UNKNOWN)
-- = NOT (UNKNOWN)
-- = UNKNOWN  ← Not TRUE, so row is excluded!
 
-- NOT EXISTS approach (SAFE with NULLs):
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);
 
-- For Carol (customer_id = 3):
-- Subquery: WHERE o.customer_id = 3
-- No rows match (NULL = 3 evaluates to UNKNOWN, not TRUE)
-- NOT EXISTS returns TRUE ✓
-- Carol is correctly returned!

NOT IN with NULLs is Dangerous

NOT EXISTS vs NOT IN Comparison
Aspect	NOT EXISTS	NOT IN
NULL in subquery	Handled correctly	Causes empty results
NULL in outer value	Works correctly	May cause issues
Semantics	Check for row absence	Value set membership
Clarity of intent	Clear (anti-join)	Can be ambiguous
Performance	Optimizer-friendly	May be equivalent
Recommendation	✓ Prefer in most cases	Use with caution

safe_not_in.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- If you must use NOT IN, explicitly exclude NULLs:
SELECT c.name
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT customer_id 
    FROM orders 
    WHERE customer_id IS NOT NULL  -- Explicit NULL exclusion
);
 
-- Or use COALESCE to handle NULLs:
SELECT c.name  
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT COALESCE(customer_id, -1) FROM orders  -- Convert NULL to sentinel
);
 
-- But NOT EXISTS is still cleaner:
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

NOT EXISTS vs LEFT JOIN + IS NULL

NOT EXISTS Approach:

not_exists_anti.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Customers without orders
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
 
-- Characteristics:
-- ✓ Clear intent (absence check)
-- ✓ Subquery is encapsulated
-- ✓ Can include complex
--   conditions in subquery
-- ✓ Works with any correlation

LEFT JOIN + IS NULL Approach:

left_join_anti.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Customers without orders  
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o 
    ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;
 
-- Characteristics:
-- ✓ Familiar JOIN syntax
-- ✓ Can access joined columns
--   (though they'll be NULL)
-- ✗ Must check for NULL on
--   NOT-NULLable join column
-- ✗ Less explicit intent

Performance Considerations:

In modern databases, the optimizer typically transforms these into the same internal execution plan (an anti-join). However, differences can emerge in specific scenarios:

anti_join_complex.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- COMPLEX CONDITION: NOT EXISTS handles complex conditions elegantly
-- Find customers with no high-value orders in the last year
 
-- NOT EXISTS (cleaner for complex conditions):
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.total > 1000
    AND o.order_date >= CURRENT_DATE - INTERVAL '1 year'
);
 
-- LEFT JOIN (requires all conditions in ON clause):
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o 
    ON o.customer_id = c.customer_id
    AND o.total > 1000
    AND o.order_date >= CURRENT_DATE - INTERVAL '1 year'
WHERE o.order_id IS NULL;
 
-- Note: Conditions MUST be in ON, not WHERE!
-- This is WRONG (filters after join):
-- LEFT JOIN orders o ON o.customer_id = c.customer_id
-- WHERE o.order_id IS NULL AND o.total > 1000  -- Won't work as intended

LEFT JOIN Anti-Join Pitfall

When to Use Each

•NOT EXISTS — Prefer when the intent is clearly 'find rows without matches'. Cleaner for complex correlation conditions. More explicit about anti-join semantics.
•LEFT JOIN + IS NULL — Use when you need to access columns from the potentially-matching table (even though they'll be NULL), or when the query naturally fits the JOIN pattern.

Combining NOT EXISTS Conditions

Complex business rules often require combining multiple NOT EXISTS conditions, or mixing NOT EXISTS with EXISTS. Understanding how these combine is essential for expressing sophisticated logic.

multiple_not_exists.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- AND with multiple NOT EXISTS
-- Find customers who have never ordered AND never left reviews
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
AND NOT EXISTS (
    SELECT 1 FROM reviews r WHERE r.customer_id = c.customer_id
);
 
-- OR with NOT EXISTS
-- Find products missing inventory OR missing price  
SELECT p.product_id, p.name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM inventory i WHERE i.product_id = p.product_id
)
OR NOT EXISTS (
    SELECT 1 FROM pricing pr WHERE pr.product_id = p.product_id
);
 
-- Mixed EXISTS and NOT EXISTS
-- Find active customers who have ordered but never returned anything
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
AND NOT EXISTS (
    SELECT 1 FROM returns r WHERE r.customer_id = c.customer_id
);
 
-- De Morgan's Law application:
-- NOT (EXISTS A OR EXISTS B) = NOT EXISTS A AND NOT EXISTS B  
-- "Not (has orders OR has reviews)" = "Doesn't have orders AND doesn't have reviews"

De Morgan's Laws in SQL

Boolean algebra's De Morgan's Laws apply: NOT (A OR B) = (NOT A) AND (NOT B), and NOT (A AND B) = (NOT A) OR (NOT B). This helps when refactoring complex EXISTS/NOT EXISTS combinations.

NOT EXISTS for Data Quality Auditing

data_quality_audits.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- AUDIT 1: Orphaned foreign keys
-- Find orders referencing non-existent customers
SELECT o.order_id, o.customer_id
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id
);
 
-- AUDIT 2: Missing required relationships
-- Find employees without department assignment
SELECT e.employee_id, e.name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM department_assignments da
    WHERE da.employee_id = e.employee_id
    AND da.end_date IS NULL  -- Current assignment
);
 
-- AUDIT 3: Incomplete records
-- Find products missing required attributes
SELECT p.product_id, p.name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM product_attributes pa
    WHERE pa.product_id = p.product_id
    AND pa.attribute_type = 'WEIGHT'
)
OR NOT EXISTS (
    SELECT 1 FROM product_attributes pa
    WHERE pa.product_id = p.product_id
    AND pa.attribute_type = 'DIMENSIONS'
);
 
-- AUDIT 4: Stale or abandoned records
-- Find carts not converted to orders within 24 hours
SELECT c.cart_id, c.created_at, c.customer_id
FROM shopping_carts c
WHERE c.created_at < CURRENT_TIMESTAMP - INTERVAL '24 hours'
AND NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.source_cart_id = c.cart_id
);
 
-- AUDIT 5: Audit trail completeness
-- Find transactions without audit log entries
SELECT t.transaction_id, t.amount, t.created_at
FROM transactions t
WHERE NOT EXISTS (
    SELECT 1 FROM audit_log al
    WHERE al.entity_type = 'TRANSACTION'
    AND al.entity_id = t.transaction_id::text
);

Automated Data Quality Checks

Optimizing NOT EXISTS Performance

While modern optimizers handle NOT EXISTS efficiently, certain patterns and indexing strategies ensure optimal performance, especially with large datasets.

Performance Best Practices

•Index the correlation columns — The correlation predicate (WHERE inner.fk = outer.pk) should use indexed columns. Without indexes, NOT EXISTS may require full table scans of the inner table for each outer row.
•Use SELECT 1 — While optimizers ignore the SELECT list, SELECT 1 is conventional and signals intent. Avoid SELECT * or complex expressions.
•Minimize subquery complexity — Keep the NOT EXISTS subquery focused. Complex JOINs or calculations within the subquery may prevent optimization.
•Consider LEFT JOIN alternative — Run EXPLAIN on both NOT EXISTS and LEFT JOIN versions. Sometimes one has a better plan for your specific data distribution.
•Filter outer query first — If possible, reduce the outer query result set before applying NOT EXISTS. This reduces the number of subquery executions.

not_exists_optimization.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- INDEX REQUIREMENT:
-- For this query to be efficient:
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- You need: CREATE INDEX idx_orders_customer_id ON orders(customer_id);
 
-- FILTER FIRST PATTERN:
-- Less efficient (NOT EXISTS checked for all customers):
SELECT c.customer_id, c.name
FROM customers c
WHERE c.signup_date >= '2024-01-01'
AND NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
 
-- More efficient (CTE filters first, though optimizer may do this anyway):
WITH recent_customers AS (
    SELECT customer_id, name
    FROM customers
    WHERE signup_date >= '2024-01-01'
)
SELECT rc.customer_id, rc.name
FROM recent_customers rc
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = rc.customer_id
);
 
-- EXAMINE EXECUTION PLAN:
EXPLAIN ANALYZE
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Look for: "Anti Join" or "Anti Semi Join" operator
-- Good sign that optimizer recognized the anti-join pattern

Summary: NOT EXISTS

We've thoroughly explored NOT EXISTS and anti-join patterns. Let's consolidate the key knowledge:

Key Takeaways

•NOT EXISTS finds absence — It returns TRUE when the subquery produces zero rows, enabling queries for orphans, gaps, and missing relationships.
•Prefer NOT EXISTS over NOT IN — NOT IN has dangerous NULL behavior that can return empty results. NOT EXISTS handles NULLs correctly.
•Two anti-join patterns — NOT EXISTS and LEFT JOIN + IS NULL are logically equivalent. NOT EXISTS is usually clearer; LEFT JOIN is sometimes more familiar.
•Division operation — Double NOT EXISTS (NOT EXISTS ... NOT EXISTS) implements relational division—finding rows related to ALL members of a set.
•Essential for data quality — NOT EXISTS queries find orphaned records, missing relationships, and integrity violations. Schedule them as automated checks.
•Index correlation columns — Performance depends on efficiently checking for non-matching rows. Indexes on correlation columns are mandatory for large tables.

Anti-Join Mastery

3 / 5