Database Management SystemsSQL Joins & Subqueries

Outer Joins: Preserving Unmatched Data

LevelIntermediate

Duration60 mins

TopicSQL Joins & Subqueries

5 / 5

NULL in Results: Mastering Outer Join Output

The NULL Challenge in Outer Joins

In outer joins, NULL takes on a specific meaning: "no matching row existed." This is different from NULL in regular data, which might mean "unknown," "not applicable," or "not entered." But SQL doesn't distinguish between these meanings—a NULL is a NULL.

This creates challenges:

How do you tell if a NULL means "unmatched" or "the value was actually NULL"?
How do calculations behave when some values are NULL?
How do comparisons work when NULL is involved?
How do you avoid subtle bugs that NULL can introduce?

This page provides comprehensive coverage of NULL in outer join results, giving you the knowledge to write correct, robust queries that handle NULL appropriately.

Learning Objectives

By completing this page, you will:\n• Understand the semantics of NULL in outer join contexts\n• Master techniques for distinguishing 'unmatched NULL' from 'data NULL'\n• Handle NULL correctly in calculations, comparisons, and aggregations\n• Use COALESCE, NULLIF, and CASE for NULL management\n• Avoid common NULL-related bugs in outer join queries\n• Apply three-valued logic correctly in complex conditions

NULL Semantics Refresher

Before diving into outer-join-specific NULL handling, let's ensure we have a solid foundation in SQL's NULL semantics.

The Fundamental Principle:

NULL represents the absence of a value. It is not zero, not an empty string, not false—it is nothing. This creates special behavior:

Operation	With NULL	Explanation
NULL = NULL	Unknown (not TRUE)	You can't compare nothing to nothing
NULL = 'value'	Unknown (not TRUE)	You can't compare nothing to something
NULL + 5	NULL	Any arithmetic with NULL produces NULL
NULL OR TRUE	TRUE	TRUE wins regardless
NULL AND TRUE	Unknown	Can't determine the result
NULL OR FALSE	Unknown	Can't determine the result
NULL AND FALSE	FALSE	FALSE wins regardless

Three-Valued Logic

SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. When NULL participates in a comparison or logical operation, the result is often UNKNOWN—and UNKNOWN is treated as 'not TRUE' for filtering purposes. This means rows with UNKNOWN conditions are excluded from WHERE results.

null_comparison_examples.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
-- NULL comparison pitfalls
-- These DO NOT do what you might expect:
 
-- Wrong: Will NEVER match any row (NULL = NULL is Unknown)
SELECT * FROM Orders WHERE customer_id = NULL;  -- Returns 0 rows
 
-- Wrong: Also never matches (Unknown is not TRUE)
SELECT * FROM Orders WHERE NULL = NULL;  -- Returns 0 rows
 
-- Correct: Use IS NULL to test for NULL
SELECT * FROM Orders WHERE customer_id IS NULL;
 
-- Correct: Use IS NOT NULL to test for non-NULL
SELECT * FROM Orders WHERE customer_id IS NOT NULL;
 
-- NULL in expressions propagates
SELECT 
    10 + NULL as result,      -- NULL
    'Hello' || NULL as text,  -- NULL (or 'Hello' in some databases)
    NULL * 0 as zero_test     -- NULL (not 0!)
 
-- NULL in IN and NOT IN
-- If the list contains NULL, NOT IN behaves unexpectedly
SELECT * FROM Customers WHERE customer_id NOT IN (1, 2, NULL);
-- Returns 0 rows! Because: 
-- customer_id NOT IN (1, 2, NULL)
-- = customer_id != 1 AND customer_id != 2 AND customer_id != NULL
-- = TRUE AND TRUE AND Unknown = Unknown

The IS NULL / IS NOT NULL Operators:

These are the only reliable ways to test for NULL in SQL:

column IS NULL       -- TRUE if column contains NULL
column IS NOT NULL   -- TRUE if column contains a value

Never use = NULL or != NULL—they always produce UNKNOWN.

Sources of NULL in Outer Join Results

In outer join results, NULL can come from multiple sources. Understanding these sources is essential for correct interpretation and handling.

Source 1: Unmatched Rows (Join-introduced NULL)

When a row from the preserved table has no match in the other table, all columns from the non-matched table are NULL.

null_source_unmatched.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Data:
-- Customers: (1, 'Alice', 'alice@email.com'), (2, 'Bob', 'bob@email.com')
-- Orders: (101, 1, 500.00)  -- Only Alice has an order
 
SELECT 
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- Result:
-- | customer_id | customer_name | order_id | amount |
-- |-------------|---------------|----------|--------|
-- | 1           | Alice         | 101      | 500.00 |
-- | 2           | Bob           | NULL     | NULL   |  ← Join-introduced NULL
--                                 ^^^^       ^^^^
--                                  Both NULL because Bob has no orders

Source 2: Original Data NULL

The source table itself may contain NULL values in nullable columns. These NULLs appear in join results regardless of whether the row matched.

null_source_data.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Data:
-- Customers: (1, 'Alice', 'alice@email.com'), (2, 'Bob', NULL)  
--            Bob has NULL email (never provided)
-- Orders: (101, 1, 500.00), (102, 2, 300.00)  -- Both have orders
 
SELECT 
    c.customer_id,
    c.customer_name,
    c.email,        -- Nullable column
    o.order_id,
    o.amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- Result:
-- | customer_id | customer_name | email           | order_id | amount |
-- |-------------|---------------|-----------------|----------|--------|
-- | 1           | Alice         | alice@email.com | 101      | 500.00 |
-- | 2           | Bob           | NULL            | 102      | 300.00 |
--                                 ^^^^
--                                  Data NULL (email was never entered)
--                                  This row DID match (has order_id)

The Ambiguity Problem:

When you see NULL in an outer join result, you cannot immediately know which source it came from:

Is email = NULL because the customer never entered an email?
Or is email = NULL because the row came from the right table and had no match?

For columns from the preserved table, this isn't usually ambiguous. But for columns from the outer-joined table, you need to check the primary key.

The Primary Key Test

A table's primary key is never NULL in the source data. If joined_table.primary_key IS NULL, the row is definitely unmatched. If joined_table.primary_key IS NOT NULL, the row matched, and any NULLs in other columns are data NULLs.

Techniques for Distinguishing NULL Sources

In production queries, you often need to explicitly handle the different sources of NULL. Here are proven techniques.

Technique 1: Check the Primary Key

The most reliable method—always check the joined table's primary key to determine match status:

null_distinguish_pk.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
-- Classify each row by match status
SELECT 
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.amount,
    o.notes,  -- Nullable column
    CASE 
        WHEN o.order_id IS NULL THEN 'No Order'
        WHEN o.notes IS NULL THEN 'Order (no notes)'
        ELSE 'Order with notes'
    END as status
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- This correctly distinguishes:
-- 1. Customer with no orders (order_id IS NULL)
-- 2. Customer with order but no notes (order_id exists, notes IS NULL)
-- 3. Customer with order and notes (both exist)
 
-- For reporting, you might want explicit labels
SELECT 
    c.customer_name,
    CASE 
        WHEN o.order_id IS NULL THEN 'N/A (No Order)'
        WHEN o.notes IS NULL THEN '(Not provided)'
        ELSE o.notes
    END as order_notes_display
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;

Technique 2: Using COALESCE with Sentinel Values

For display purposes, replace NULLs with meaningful text or default values:

null_coalesce_sentinel.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
-- Different defaults for different NULL sources
SELECT 
    c.customer_name,
    -- Order amount: 0 for no orders, actual value otherwise
    COALESCE(o.amount, 0) as order_amount,
    
    -- Notes: distinguish between no order and no notes
    CASE 
        WHEN o.order_id IS NULL THEN 'No order placed'
        ELSE COALESCE(o.notes, 'No notes provided')
    END as notes_display,
    
    -- Shipping address: might be NULL in data
    COALESCE(o.shipping_address, c.default_address, 'No address') as ship_to
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- Multi-source fallback with COALESCE
SELECT 
    p.product_name,
    COALESCE(
        custom_price.amount,     -- Customer-specific price if exists
        category_price.amount,   -- Category default price if exists
        p.list_price             -- Product list price as final fallback
    ) as effective_price
FROM Products p
LEFT JOIN CustomerPricing custom_price 
    ON p.product_id = custom_price.product_id 
    AND custom_price.customer_id = 123
LEFT JOIN CategoryPricing category_price 
    ON p.category_id = category_price.category_id;

Technique 3: Adding Explicit Match Indicators

Include a column that explicitly states whether a match occurred:

null_match_indicator.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
-- Explicit match indicator columns
SELECT 
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.amount,
    (o.order_id IS NOT NULL) as has_orders,  -- Boolean indicator
    CASE 
        WHEN o.order_id IS NOT NULL THEN 'Matched'
        ELSE 'Unmatched'
    END as match_status
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- Use in downstream logic
WITH OrderStatus AS (
    SELECT 
        c.customer_id,
        c.customer_name,
        (o.order_id IS NOT NULL) as is_active_customer
    FROM Customers c
    LEFT JOIN Orders o ON c.customer_id = o.customer_id
)
SELECT 
    customer_name,
    CASE is_active_customer 
        WHEN TRUE THEN 'Send loyalty rewards'
        ELSE 'Send re-engagement campaign'
    END as marketing_action
FROM OrderStatus;

NULL in Calculations and Expressions

NULL propagates through calculations in ways that can produce unexpected results. Understanding this propagation is essential for writing correct analytical queries.

The Propagation Rule:

Almost any expression involving NULL produces NULL:

NULL + 10 = NULL
NULL * 0 = NULL  (even though mathematically 0 times anything is 0)
NULL || 'text' = NULL  (in standard SQL; varies by database)
100 / NULL = NULL

null_calculation_problems.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
-- PROBLEM: NULL ruins calculated totals
SELECT 
    c.customer_name,
    o.amount,
    o.tax,
    o.shipping,
    o.amount + o.tax + o.shipping as total  -- NULL if ANY component is NULL
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- If a customer has no orders:
-- amount=NULL, tax=NULL, shipping=NULL
-- total = NULL + NULL + NULL = NULL
 
-- Even if they had an order but shipping was NULL:
-- amount=100, tax=8, shipping=NULL
-- total = 100 + 8 + NULL = NULL  ← The whole total is lost!
 
-- SOLUTION: Use COALESCE for each component
SELECT 
    c.customer_name,
    COALESCE(o.amount, 0) as amount,
    COALESCE(o.tax, 0) as tax,
    COALESCE(o.shipping, 0) as shipping,
    COALESCE(o.amount, 0) + COALESCE(o.tax, 0) + COALESCE(o.shipping, 0) as total
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- Now zeros are used where NULLs would have been

The NULL Propagation Trap

One NULL in a chain of calculations makes the entire result NULL. If you're summing 10 values and one is NULL, the sum is NULL. Always wrap nullable values in COALESCE before arithmetic.

NULL in Aggregate Functions:

Aggregate functions handle NULL specially—they simply ignore NULL values (except COUNT(*)):

null_in_aggregates.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
-- Aggregate function NULL behavior
-- Sample data: amounts = [100, NULL, 200, NULL, 150]
 
SELECT 
    COUNT(*) as row_count,           -- 5 (counts all rows)
    COUNT(amount) as value_count,    -- 3 (counts non-NULL values)
    SUM(amount) as total,            -- 450 (sums non-NULL values)
    AVG(amount) as average,          -- 150 (450/3, NULLs excluded)
    MIN(amount) as minimum,          -- 100
    MAX(amount) as maximum           -- 200
FROM SampleData;
 
-- IMPORTANT: If ALL values are NULL:
-- COUNT(*) = row count
-- COUNT(column) = 0
-- SUM(column) = NULL (not 0!)
-- AVG(column) = NULL
-- MIN(column) = NULL
-- MAX(column) = NULL
 
-- Outer join aggregation with all-NULL groups
SELECT 
    d.department_name,
    SUM(s.amount) as total_sales  -- NULL if no sales (not 0!)
FROM Departments d
LEFT JOIN Sales s ON d.department_id = s.department_id
GROUP BY d.department_id, d.department_name;
 
-- FIX: Always COALESCE aggregate results
SELECT 
    d.department_name,
    COALESCE(SUM(s.amount), 0) as total_sales  -- 0 if no sales
FROM Departments d
LEFT JOIN Sales s ON d.department_id = s.department_id
GROUP BY d.department_id, d.department_name;

Aggregate Function NULL Handling
Function	NULL Treatment	All NULL Result
COUNT(*)	Counts rows, NULLs included	Row count (never NULL)
COUNT(column)	Counts non-NULL values	0
SUM(column)	Sums non-NULL values	NULL
AVG(column)	Averages non-NULL values	NULL
MIN(column)	Finds min of non-NULL values	NULL
MAX(column)	Finds max of non-NULL values	NULL

NULL in Comparisons and Conditions

NULL creates three-valued logic: TRUE, FALSE, and UNKNOWN. Understanding how UNKNOWN propagates through logical expressions is essential for writing correct WHERE and HAVING clauses.

The Three-Valued Logic Truth Tables:

AND with NULL (UNKNOWN)
A	NULL	Result
TRUE	AND	UNKNOWN
FALSE	AND	FALSE
UNKNOWN	AND	UNKNOWN

OR with NULL (UNKNOWN)
A	NULL	Result
TRUE	OR	TRUE
FALSE	OR	UNKNOWN
UNKNOWN	OR	UNKNOWN

null_in_conditions.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
-- Filtering with NULL-containing results
 
-- This query EXCLUDES unmatched rows:
SELECT c.customer_name, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.amount > 100;  
-- For unmatched: NULL > 100 = UNKNOWN, row excluded
 
-- This query ALSO excludes unmatched rows:
SELECT c.customer_name, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.amount <= 100 OR o.amount > 100;  
-- For unmatched: UNKNOWN OR UNKNOWN = UNKNOWN, row excluded!
-- You might think this covers all cases, but it doesn't cover NULL
 
-- To include unmatched rows, explicitly handle NULL:
SELECT c.customer_name, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.amount > 100 OR o.order_id IS NULL;
-- Now unmatched rows are included
 
-- OR use COALESCE to replace NULL with a value that passes the filter:
SELECT c.customer_name, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE COALESCE(o.amount, 0) > 100;
-- Unmatched rows have amount treated as 0, excluded by > 100
-- (Only do this if you WANT to exclude unmatched)

Negation Is Tricky

NOT UNKNOWN = UNKNOWN. So WHERE NOT (o.amount > 100) still excludes rows where o.amount is NULL. If you want rows where amount is NOT greater than 100 OR there is no order, you must explicitly add OR o.order_id IS NULL.

BETWEEN, IN, and LIKE with NULL:

null_in_special_operators.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
-- BETWEEN and NULL
SELECT * FROM Orders o 
WHERE amount BETWEEN 100 AND 500;
-- NULL amounts produce UNKNOWN, excluded
 
-- IN and NULL
SELECT * FROM Products 
WHERE category IN ('Electronics', 'Clothing', NULL);
-- Products with NULL category: NULL IN (...) = UNKNOWN, excluded!
 
-- NOT IN and NULL (the dangerous case)
SELECT * FROM Products 
WHERE category NOT IN ('Electronics', NULL);
-- For ANY product: category != 'Electronics' AND category != NULL
-- The second part is UNKNOWN, so the whole thing is UNKNOWN
-- RESULT: NO ROWS RETURNED for any category!
 
-- SAFE NOT IN: Exclude NULLs from the list
SELECT * FROM Products 
WHERE category NOT IN (
    SELECT category FROM ExcludedCategories WHERE category IS NOT NULL
);
 
-- LIKE and NULL
SELECT * FROM Customers WHERE email LIKE '%@gmail.com';
-- NULL emails produce UNKNOWN, excluded from results

NULL Handling Functions and Techniques

SQL provides several functions specifically designed to handle NULL values. Mastering these is essential for robust outer join queries.

COALESCE: Return First Non-NULL Value

COALESCE takes multiple arguments and returns the first non-NULL value.

coalesce_usage.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Basic COALESCE: single fallback
SELECT COALESCE(o.amount, 0) as amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- Multiple fallbacks: first non-NULL wins
SELECT COALESCE(
    customer_specified_price,
    promotional_price,
    category_price,
    list_price
) as effective_price
FROM ProductPricing;
 
-- COALESCE for display text
SELECT COALESCE(o.notes, '(No notes entered)') as notes
FROM Orders o;
 
-- COALESCE to unify outer join keys
SELECT COALESCE(left_table.id, right_table.id) as unified_id
FROM left_table
FULL OUTER JOIN right_table ON left_table.id = right_table.id;

NULLIF: Create NULL from Value

NULLIF returns NULL if two expressions are equal; otherwise returns the first expression. Useful for avoiding division by zero or replacing placeholder values.

nullif_usage.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Avoid division by zero
SELECT revenue / NULLIF(costs, 0) as margin
FROM Financials;
-- If costs = 0, NULLIF returns NULL, and NULL/NULL = NULL
-- Better than an error!
 
-- Replace placeholder values with NULL
SELECT NULLIF(phone_number, 'N/A') as phone
FROM Customers;
-- 'N/A' becomes NULL, actual numbers remain
 
-- Remove sentinel values
SELECT NULLIF(age, -1) as age  -- -1 was used for "unknown"
FROM OldSystem;
 
-- Combine with COALESCE for full transformation
SELECT COALESCE(NULLIF(old_value, 'UNKNOWN'), 'Not Available') as cleaned
FROM LegacyData;

CASE with NULL:

CASE expressions provide the most flexible NULL handling, allowing complex logic:

case_null_handling.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
-- CASE for complex NULL logic
SELECT 
    customer_name,
    order_id,
    amount,
    CASE 
        WHEN order_id IS NULL THEN 'Never ordered'
        WHEN amount IS NULL THEN 'Order amount unknown'
        WHEN amount = 0 THEN 'Zero-value order'
        WHEN amount < 100 THEN 'Small order'
        WHEN amount < 1000 THEN 'Medium order'
        ELSE 'Large order'
    END as order_category
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- CASE with searched conditions handles NULL naturally
-- because IS NULL is a valid condition
 
-- NULL-safe comparison using CASE
SELECT *
FROM Table1 t1
JOIN Table2 t2 
ON CASE 
    WHEN t1.nullable_col IS NULL AND t2.nullable_col IS NULL THEN TRUE
    WHEN t1.nullable_col IS NULL OR t2.nullable_col IS NULL THEN FALSE
    ELSE t1.nullable_col = t2.nullable_col
END;
-- Treats two NULLs as equal (unlike default behavior)

NULL Handling Function Summary
Function	Purpose	Example Result
COALESCE(a, b, c)	First non-NULL value	COALESCE(NULL, 5, 3) → 5
NULLIF(a, b)	NULL if a = b, else a	NULLIF(5, 5) → NULL
IFNULL(a, b) / NVL(a, b)	b if a is NULL (vendor-specific)	NVL(NULL, 0) → 0
IS NULL	Test for NULL	NULL IS NULL → TRUE
IS DISTINCT FROM	NULL-safe inequality (some DBs)	NULL IS DISTINCT FROM 5 → TRUE

Common NULL Bugs and How to Avoid Them

Experienced developers have learned these lessons the hard way. Learn from their mistakes.

Bug 1: NOT IN with NULL Values

bug_not_in_null.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
-- BUG: NOT IN fails silently when subquery contains NULL
SELECT product_name
FROM Products
WHERE product_id NOT IN (
    SELECT product_id FROM Discontinued  -- May contain NULL
);
-- If Discontinued has any NULL product_id, this returns ZERO rows!
 
-- FIX 1: Exclude NULL in subquery
SELECT product_name
FROM Products
WHERE product_id NOT IN (
    SELECT product_id FROM Discontinued WHERE product_id IS NOT NULL
);
 
-- FIX 2: Use NOT EXISTS instead (NULL-safe)
SELECT product_name
FROM Products p
WHERE NOT EXISTS (
    SELECT 1 FROM Discontinued d WHERE d.product_id = p.product_id
);
 
-- FIX 3: Use LEFT JOIN + IS NULL
SELECT p.product_name
FROM Products p
LEFT JOIN Discontinued d ON p.product_id = d.product_id
WHERE d.product_id IS NULL;

Bug 2: Counting All Rows vs Non-NULL Values

bug_count_null.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- BUG: Using COUNT(*) when you meant COUNT(column)
SELECT 
    department_id,
    COUNT(*) as employee_count  -- Counts all rows, including NULLs
FROM Employees
LEFT JOIN Departments ON ...
GROUP BY department_id;
-- Departments with no employees show count 1 (the NULL row is counted)
 
-- FIX: Use COUNT(specific_column)
SELECT 
    d.department_id,
    COUNT(e.employee_id) as employee_count  -- Counts non-NULL only
FROM Departments d
LEFT JOIN Employees e ON d.department_id = e.department_id
GROUP BY d.department_id;
-- Now departments with no employees show count 0

Bug 3: Accidental INNER JOIN via WHERE

bug_where_null.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- BUG: Filtering on outer-joined column in WHERE
SELECT c.customer_name, o.order_date
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01';
-- Customers without orders have NULL order_date
-- NULL >= '2024-01-01' is UNKNOWN, so they're excluded
 
-- FIX: Move condition to ON clause
SELECT c.customer_name, o.order_date
FROM Customers c
LEFT JOIN Orders o 
    ON c.customer_id = o.customer_id
    AND o.order_date >= '2024-01-01';
-- All customers appear; only recent orders are joined

The NULL Bug Checklist

•Check NOT IN subqueries — Does the subquery ever return NULL? Use NOT EXISTS or filter NULLs.
•Verify aggregate functions — Are you using COALESCE around SUM/AVG? Are you using COUNT(column) vs COUNT(*)?
•Review WHERE conditions — Are you filtering on outer-joined columns? Should conditions be in ON?
•Test with 'no match' cases — Do your test queries include rows that have no matches?
•Audit calculations — Are any nullable columns used in arithmetic without COALESCE?

Summary: NULL in Outer Join Results

NULL handling is one of the trickiest aspects of SQL, and outer joins amplify the complexity by introducing NULLs systematically. Mastering NULL is essential for writing correct, robust queries.

Key Takeaways

•NULL can come from two sources in outer joins: unmatched rows (join-introduced) or actual data NULLs.
•Check the primary key to determine if a row was matched—PKs are never NULL in source data.
•NULL propagates through calculations — one NULL makes the entire expression NULL.
•Aggregate functions ignore NULL (except COUNT(*)), but return NULL if ALL values are NULL.
•Three-valued logic (TRUE/FALSE/UNKNOWN) affects WHERE filtering—UNKNOWN rows are excluded.
•Use COALESCE liberally for defaults, NULLIF for sentinel replacement, and CASE for complex logic.
•Watch for NOT IN bugs — subqueries with NULL values cause NOT IN to return zero rows.
•Test with edge cases — always verify your queries with rows that have no matches.

Module Complete

Congratulations! You've completed the comprehensive module on Outer Joins. You now understand LEFT, RIGHT, and FULL OUTER JOIN, how row preservation works, and how to handle the NULL values that outer joins produce.

These skills enable you to answer a category of questions that INNER JOIN cannot address—finding what's missing, creating complete reports, and reconciling data across sources.

Module Complete

You've mastered outer joins—from LEFT to RIGHT to FULL, from row preservation to NULL handling. These are foundational SQL skills that you'll use throughout your career. The next module explores other specialized join types: CROSS JOIN, NATURAL JOIN, and self-referential joins.

5 / 5

Loading learning content...

Database Management SystemsSQL Joins & Subqueries

Outer Joins: Preserving Unmatched Data

LevelIntermediate

Duration60 mins

TopicSQL Joins & Subqueries

5 / 5

NULL in Results: Mastering Outer Join Output

The NULL Challenge in Outer Joins

This creates challenges:

How do you tell if a NULL means "unmatched" or "the value was actually NULL"?
How do calculations behave when some values are NULL?
How do comparisons work when NULL is involved?
How do you avoid subtle bugs that NULL can introduce?

This page provides comprehensive coverage of NULL in outer join results, giving you the knowledge to write correct, robust queries that handle NULL appropriately.

Learning Objectives

NULL Semantics Refresher

Before diving into outer-join-specific NULL handling, let's ensure we have a solid foundation in SQL's NULL semantics.

The Fundamental Principle:

NULL represents the absence of a value. It is not zero, not an empty string, not false—it is nothing. This creates special behavior:

Operation	With NULL	Explanation
NULL = NULL	Unknown (not TRUE)	You can't compare nothing to nothing
NULL = 'value'	Unknown (not TRUE)	You can't compare nothing to something
NULL + 5	NULL	Any arithmetic with NULL produces NULL
NULL OR TRUE	TRUE	TRUE wins regardless
NULL AND TRUE	Unknown	Can't determine the result
NULL OR FALSE	Unknown	Can't determine the result
NULL AND FALSE	FALSE	FALSE wins regardless

Three-Valued Logic

null_comparison_examples.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
-- NULL comparison pitfalls
-- These DO NOT do what you might expect:
 
-- Wrong: Will NEVER match any row (NULL = NULL is Unknown)
SELECT * FROM Orders WHERE customer_id = NULL;  -- Returns 0 rows
 
-- Wrong: Also never matches (Unknown is not TRUE)
SELECT * FROM Orders WHERE NULL = NULL;  -- Returns 0 rows
 
-- Correct: Use IS NULL to test for NULL
SELECT * FROM Orders WHERE customer_id IS NULL;
 
-- Correct: Use IS NOT NULL to test for non-NULL
SELECT * FROM Orders WHERE customer_id IS NOT NULL;
 
-- NULL in expressions propagates
SELECT 
    10 + NULL as result,      -- NULL
    'Hello' || NULL as text,  -- NULL (or 'Hello' in some databases)
    NULL * 0 as zero_test     -- NULL (not 0!)
 
-- NULL in IN and NOT IN
-- If the list contains NULL, NOT IN behaves unexpectedly
SELECT * FROM Customers WHERE customer_id NOT IN (1, 2, NULL);
-- Returns 0 rows! Because: 
-- customer_id NOT IN (1, 2, NULL)
-- = customer_id != 1 AND customer_id != 2 AND customer_id != NULL
-- = TRUE AND TRUE AND Unknown = Unknown

The IS NULL / IS NOT NULL Operators:

These are the only reliable ways to test for NULL in SQL:

column IS NULL       -- TRUE if column contains NULL
column IS NOT NULL   -- TRUE if column contains a value

Never use = NULL or != NULL—they always produce UNKNOWN.

Sources of NULL in Outer Join Results

In outer join results, NULL can come from multiple sources. Understanding these sources is essential for correct interpretation and handling.

Source 1: Unmatched Rows (Join-introduced NULL)

When a row from the preserved table has no match in the other table, all columns from the non-matched table are NULL.

null_source_unmatched.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Data:
-- Customers: (1, 'Alice', 'alice@email.com'), (2, 'Bob', 'bob@email.com')
-- Orders: (101, 1, 500.00)  -- Only Alice has an order
 
SELECT 
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- Result:
-- | customer_id | customer_name | order_id | amount |
-- |-------------|---------------|----------|--------|
-- | 1           | Alice         | 101      | 500.00 |
-- | 2           | Bob           | NULL     | NULL   |  ← Join-introduced NULL
--                                 ^^^^       ^^^^
--                                  Both NULL because Bob has no orders

Source 2: Original Data NULL

The source table itself may contain NULL values in nullable columns. These NULLs appear in join results regardless of whether the row matched.

null_source_data.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Data:
-- Customers: (1, 'Alice', 'alice@email.com'), (2, 'Bob', NULL)  
--            Bob has NULL email (never provided)
-- Orders: (101, 1, 500.00), (102, 2, 300.00)  -- Both have orders
 
SELECT 
    c.customer_id,
    c.customer_name,
    c.email,        -- Nullable column
    o.order_id,
    o.amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- Result:
-- | customer_id | customer_name | email           | order_id | amount |
-- |-------------|---------------|-----------------|----------|--------|
-- | 1           | Alice         | alice@email.com | 101      | 500.00 |
-- | 2           | Bob           | NULL            | 102      | 300.00 |
--                                 ^^^^
--                                  Data NULL (email was never entered)
--                                  This row DID match (has order_id)

The Ambiguity Problem:

When you see NULL in an outer join result, you cannot immediately know which source it came from:

Is email = NULL because the customer never entered an email?
Or is email = NULL because the row came from the right table and had no match?

For columns from the preserved table, this isn't usually ambiguous. But for columns from the outer-joined table, you need to check the primary key.

The Primary Key Test

Techniques for Distinguishing NULL Sources

In production queries, you often need to explicitly handle the different sources of NULL. Here are proven techniques.

Technique 1: Check the Primary Key

The most reliable method—always check the joined table's primary key to determine match status:

null_distinguish_pk.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
-- Classify each row by match status
SELECT 
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.amount,
    o.notes,  -- Nullable column
    CASE 
        WHEN o.order_id IS NULL THEN 'No Order'
        WHEN o.notes IS NULL THEN 'Order (no notes)'
        ELSE 'Order with notes'
    END as status
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- This correctly distinguishes:
-- 1. Customer with no orders (order_id IS NULL)
-- 2. Customer with order but no notes (order_id exists, notes IS NULL)
-- 3. Customer with order and notes (both exist)
 
-- For reporting, you might want explicit labels
SELECT 
    c.customer_name,
    CASE 
        WHEN o.order_id IS NULL THEN 'N/A (No Order)'
        WHEN o.notes IS NULL THEN '(Not provided)'
        ELSE o.notes
    END as order_notes_display
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;

Technique 2: Using COALESCE with Sentinel Values

For display purposes, replace NULLs with meaningful text or default values:

null_coalesce_sentinel.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
-- Different defaults for different NULL sources
SELECT 
    c.customer_name,
    -- Order amount: 0 for no orders, actual value otherwise
    COALESCE(o.amount, 0) as order_amount,
    
    -- Notes: distinguish between no order and no notes
    CASE 
        WHEN o.order_id IS NULL THEN 'No order placed'
        ELSE COALESCE(o.notes, 'No notes provided')
    END as notes_display,
    
    -- Shipping address: might be NULL in data
    COALESCE(o.shipping_address, c.default_address, 'No address') as ship_to
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- Multi-source fallback with COALESCE
SELECT 
    p.product_name,
    COALESCE(
        custom_price.amount,     -- Customer-specific price if exists
        category_price.amount,   -- Category default price if exists
        p.list_price             -- Product list price as final fallback
    ) as effective_price
FROM Products p
LEFT JOIN CustomerPricing custom_price 
    ON p.product_id = custom_price.product_id 
    AND custom_price.customer_id = 123
LEFT JOIN CategoryPricing category_price 
    ON p.category_id = category_price.category_id;

Technique 3: Adding Explicit Match Indicators

Include a column that explicitly states whether a match occurred:

null_match_indicator.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
-- Explicit match indicator columns
SELECT 
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.amount,
    (o.order_id IS NOT NULL) as has_orders,  -- Boolean indicator
    CASE 
        WHEN o.order_id IS NOT NULL THEN 'Matched'
        ELSE 'Unmatched'
    END as match_status
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- Use in downstream logic
WITH OrderStatus AS (
    SELECT 
        c.customer_id,
        c.customer_name,
        (o.order_id IS NOT NULL) as is_active_customer
    FROM Customers c
    LEFT JOIN Orders o ON c.customer_id = o.customer_id
)
SELECT 
    customer_name,
    CASE is_active_customer 
        WHEN TRUE THEN 'Send loyalty rewards'
        ELSE 'Send re-engagement campaign'
    END as marketing_action
FROM OrderStatus;

NULL in Calculations and Expressions

NULL propagates through calculations in ways that can produce unexpected results. Understanding this propagation is essential for writing correct analytical queries.

The Propagation Rule:

Almost any expression involving NULL produces NULL:

NULL + 10 = NULL
NULL * 0 = NULL  (even though mathematically 0 times anything is 0)
NULL || 'text' = NULL  (in standard SQL; varies by database)
100 / NULL = NULL

null_calculation_problems.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
-- PROBLEM: NULL ruins calculated totals
SELECT 
    c.customer_name,
    o.amount,
    o.tax,
    o.shipping,
    o.amount + o.tax + o.shipping as total  -- NULL if ANY component is NULL
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- If a customer has no orders:
-- amount=NULL, tax=NULL, shipping=NULL
-- total = NULL + NULL + NULL = NULL
 
-- Even if they had an order but shipping was NULL:
-- amount=100, tax=8, shipping=NULL
-- total = 100 + 8 + NULL = NULL  ← The whole total is lost!
 
-- SOLUTION: Use COALESCE for each component
SELECT 
    c.customer_name,
    COALESCE(o.amount, 0) as amount,
    COALESCE(o.tax, 0) as tax,
    COALESCE(o.shipping, 0) as shipping,
    COALESCE(o.amount, 0) + COALESCE(o.tax, 0) + COALESCE(o.shipping, 0) as total
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- Now zeros are used where NULLs would have been

The NULL Propagation Trap

One NULL in a chain of calculations makes the entire result NULL. If you're summing 10 values and one is NULL, the sum is NULL. Always wrap nullable values in COALESCE before arithmetic.

NULL in Aggregate Functions:

Aggregate functions handle NULL specially—they simply ignore NULL values (except COUNT(*)):

null_in_aggregates.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
-- Aggregate function NULL behavior
-- Sample data: amounts = [100, NULL, 200, NULL, 150]
 
SELECT 
    COUNT(*) as row_count,           -- 5 (counts all rows)
    COUNT(amount) as value_count,    -- 3 (counts non-NULL values)
    SUM(amount) as total,            -- 450 (sums non-NULL values)
    AVG(amount) as average,          -- 150 (450/3, NULLs excluded)
    MIN(amount) as minimum,          -- 100
    MAX(amount) as maximum           -- 200
FROM SampleData;
 
-- IMPORTANT: If ALL values are NULL:
-- COUNT(*) = row count
-- COUNT(column) = 0
-- SUM(column) = NULL (not 0!)
-- AVG(column) = NULL
-- MIN(column) = NULL
-- MAX(column) = NULL
 
-- Outer join aggregation with all-NULL groups
SELECT 
    d.department_name,
    SUM(s.amount) as total_sales  -- NULL if no sales (not 0!)
FROM Departments d
LEFT JOIN Sales s ON d.department_id = s.department_id
GROUP BY d.department_id, d.department_name;
 
-- FIX: Always COALESCE aggregate results
SELECT 
    d.department_name,
    COALESCE(SUM(s.amount), 0) as total_sales  -- 0 if no sales
FROM Departments d
LEFT JOIN Sales s ON d.department_id = s.department_id
GROUP BY d.department_id, d.department_name;

Aggregate Function NULL Handling
Function	NULL Treatment	All NULL Result
COUNT(*)	Counts rows, NULLs included	Row count (never NULL)
COUNT(column)	Counts non-NULL values	0
SUM(column)	Sums non-NULL values	NULL
AVG(column)	Averages non-NULL values	NULL
MIN(column)	Finds min of non-NULL values	NULL
MAX(column)	Finds max of non-NULL values	NULL

NULL in Comparisons and Conditions

NULL creates three-valued logic: TRUE, FALSE, and UNKNOWN. Understanding how UNKNOWN propagates through logical expressions is essential for writing correct WHERE and HAVING clauses.

The Three-Valued Logic Truth Tables:

AND with NULL (UNKNOWN)
A	NULL	Result
TRUE	AND	UNKNOWN
FALSE	AND	FALSE
UNKNOWN	AND	UNKNOWN

OR with NULL (UNKNOWN)
A	NULL	Result
TRUE	OR	TRUE
FALSE	OR	UNKNOWN
UNKNOWN	OR	UNKNOWN

null_in_conditions.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
-- Filtering with NULL-containing results
 
-- This query EXCLUDES unmatched rows:
SELECT c.customer_name, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.amount > 100;  
-- For unmatched: NULL > 100 = UNKNOWN, row excluded
 
-- This query ALSO excludes unmatched rows:
SELECT c.customer_name, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.amount <= 100 OR o.amount > 100;  
-- For unmatched: UNKNOWN OR UNKNOWN = UNKNOWN, row excluded!
-- You might think this covers all cases, but it doesn't cover NULL
 
-- To include unmatched rows, explicitly handle NULL:
SELECT c.customer_name, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.amount > 100 OR o.order_id IS NULL;
-- Now unmatched rows are included
 
-- OR use COALESCE to replace NULL with a value that passes the filter:
SELECT c.customer_name, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE COALESCE(o.amount, 0) > 100;
-- Unmatched rows have amount treated as 0, excluded by > 100
-- (Only do this if you WANT to exclude unmatched)

Negation Is Tricky

BETWEEN, IN, and LIKE with NULL:

null_in_special_operators.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
-- BETWEEN and NULL
SELECT * FROM Orders o 
WHERE amount BETWEEN 100 AND 500;
-- NULL amounts produce UNKNOWN, excluded
 
-- IN and NULL
SELECT * FROM Products 
WHERE category IN ('Electronics', 'Clothing', NULL);
-- Products with NULL category: NULL IN (...) = UNKNOWN, excluded!
 
-- NOT IN and NULL (the dangerous case)
SELECT * FROM Products 
WHERE category NOT IN ('Electronics', NULL);
-- For ANY product: category != 'Electronics' AND category != NULL
-- The second part is UNKNOWN, so the whole thing is UNKNOWN
-- RESULT: NO ROWS RETURNED for any category!
 
-- SAFE NOT IN: Exclude NULLs from the list
SELECT * FROM Products 
WHERE category NOT IN (
    SELECT category FROM ExcludedCategories WHERE category IS NOT NULL
);
 
-- LIKE and NULL
SELECT * FROM Customers WHERE email LIKE '%@gmail.com';
-- NULL emails produce UNKNOWN, excluded from results

NULL Handling Functions and Techniques

SQL provides several functions specifically designed to handle NULL values. Mastering these is essential for robust outer join queries.

COALESCE: Return First Non-NULL Value

COALESCE takes multiple arguments and returns the first non-NULL value.

coalesce_usage.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Basic COALESCE: single fallback
SELECT COALESCE(o.amount, 0) as amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- Multiple fallbacks: first non-NULL wins
SELECT COALESCE(
    customer_specified_price,
    promotional_price,
    category_price,
    list_price
) as effective_price
FROM ProductPricing;
 
-- COALESCE for display text
SELECT COALESCE(o.notes, '(No notes entered)') as notes
FROM Orders o;
 
-- COALESCE to unify outer join keys
SELECT COALESCE(left_table.id, right_table.id) as unified_id
FROM left_table
FULL OUTER JOIN right_table ON left_table.id = right_table.id;

NULLIF: Create NULL from Value

NULLIF returns NULL if two expressions are equal; otherwise returns the first expression. Useful for avoiding division by zero or replacing placeholder values.

nullif_usage.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Avoid division by zero
SELECT revenue / NULLIF(costs, 0) as margin
FROM Financials;
-- If costs = 0, NULLIF returns NULL, and NULL/NULL = NULL
-- Better than an error!
 
-- Replace placeholder values with NULL
SELECT NULLIF(phone_number, 'N/A') as phone
FROM Customers;
-- 'N/A' becomes NULL, actual numbers remain
 
-- Remove sentinel values
SELECT NULLIF(age, -1) as age  -- -1 was used for "unknown"
FROM OldSystem;
 
-- Combine with COALESCE for full transformation
SELECT COALESCE(NULLIF(old_value, 'UNKNOWN'), 'Not Available') as cleaned
FROM LegacyData;

CASE with NULL:

CASE expressions provide the most flexible NULL handling, allowing complex logic:

case_null_handling.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
-- CASE for complex NULL logic
SELECT 
    customer_name,
    order_id,
    amount,
    CASE 
        WHEN order_id IS NULL THEN 'Never ordered'
        WHEN amount IS NULL THEN 'Order amount unknown'
        WHEN amount = 0 THEN 'Zero-value order'
        WHEN amount < 100 THEN 'Small order'
        WHEN amount < 1000 THEN 'Medium order'
        ELSE 'Large order'
    END as order_category
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
 
-- CASE with searched conditions handles NULL naturally
-- because IS NULL is a valid condition
 
-- NULL-safe comparison using CASE
SELECT *
FROM Table1 t1
JOIN Table2 t2 
ON CASE 
    WHEN t1.nullable_col IS NULL AND t2.nullable_col IS NULL THEN TRUE
    WHEN t1.nullable_col IS NULL OR t2.nullable_col IS NULL THEN FALSE
    ELSE t1.nullable_col = t2.nullable_col
END;
-- Treats two NULLs as equal (unlike default behavior)

NULL Handling Function Summary
Function	Purpose	Example Result
COALESCE(a, b, c)	First non-NULL value	COALESCE(NULL, 5, 3) → 5
NULLIF(a, b)	NULL if a = b, else a	NULLIF(5, 5) → NULL
IFNULL(a, b) / NVL(a, b)	b if a is NULL (vendor-specific)	NVL(NULL, 0) → 0
IS NULL	Test for NULL	NULL IS NULL → TRUE
IS DISTINCT FROM	NULL-safe inequality (some DBs)	NULL IS DISTINCT FROM 5 → TRUE

Common NULL Bugs and How to Avoid Them

Experienced developers have learned these lessons the hard way. Learn from their mistakes.

Bug 1: NOT IN with NULL Values

bug_not_in_null.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
-- BUG: NOT IN fails silently when subquery contains NULL
SELECT product_name
FROM Products
WHERE product_id NOT IN (
    SELECT product_id FROM Discontinued  -- May contain NULL
);
-- If Discontinued has any NULL product_id, this returns ZERO rows!
 
-- FIX 1: Exclude NULL in subquery
SELECT product_name
FROM Products
WHERE product_id NOT IN (
    SELECT product_id FROM Discontinued WHERE product_id IS NOT NULL
);
 
-- FIX 2: Use NOT EXISTS instead (NULL-safe)
SELECT product_name
FROM Products p
WHERE NOT EXISTS (
    SELECT 1 FROM Discontinued d WHERE d.product_id = p.product_id
);
 
-- FIX 3: Use LEFT JOIN + IS NULL
SELECT p.product_name
FROM Products p
LEFT JOIN Discontinued d ON p.product_id = d.product_id
WHERE d.product_id IS NULL;

Bug 2: Counting All Rows vs Non-NULL Values

bug_count_null.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- BUG: Using COUNT(*) when you meant COUNT(column)
SELECT 
    department_id,
    COUNT(*) as employee_count  -- Counts all rows, including NULLs
FROM Employees
LEFT JOIN Departments ON ...
GROUP BY department_id;
-- Departments with no employees show count 1 (the NULL row is counted)
 
-- FIX: Use COUNT(specific_column)
SELECT 
    d.department_id,
    COUNT(e.employee_id) as employee_count  -- Counts non-NULL only
FROM Departments d
LEFT JOIN Employees e ON d.department_id = e.department_id
GROUP BY d.department_id;
-- Now departments with no employees show count 0

Bug 3: Accidental INNER JOIN via WHERE

bug_where_null.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- BUG: Filtering on outer-joined column in WHERE
SELECT c.customer_name, o.order_date
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01';
-- Customers without orders have NULL order_date
-- NULL >= '2024-01-01' is UNKNOWN, so they're excluded
 
-- FIX: Move condition to ON clause
SELECT c.customer_name, o.order_date
FROM Customers c
LEFT JOIN Orders o 
    ON c.customer_id = o.customer_id
    AND o.order_date >= '2024-01-01';
-- All customers appear; only recent orders are joined

The NULL Bug Checklist

•Check NOT IN subqueries — Does the subquery ever return NULL? Use NOT EXISTS or filter NULLs.
•Verify aggregate functions — Are you using COALESCE around SUM/AVG? Are you using COUNT(column) vs COUNT(*)?
•Review WHERE conditions — Are you filtering on outer-joined columns? Should conditions be in ON?
•Test with 'no match' cases — Do your test queries include rows that have no matches?
•Audit calculations — Are any nullable columns used in arithmetic without COALESCE?

Summary: NULL in Outer Join Results

NULL handling is one of the trickiest aspects of SQL, and outer joins amplify the complexity by introducing NULLs systematically. Mastering NULL is essential for writing correct, robust queries.

Key Takeaways

•NULL can come from two sources in outer joins: unmatched rows (join-introduced) or actual data NULLs.
•Check the primary key to determine if a row was matched—PKs are never NULL in source data.
•NULL propagates through calculations — one NULL makes the entire expression NULL.
•Aggregate functions ignore NULL (except COUNT(*)), but return NULL if ALL values are NULL.
•Three-valued logic (TRUE/FALSE/UNKNOWN) affects WHERE filtering—UNKNOWN rows are excluded.
•Use COALESCE liberally for defaults, NULLIF for sentinel replacement, and CASE for complex logic.
•Watch for NOT IN bugs — subqueries with NULL values cause NOT IN to return zero rows.
•Test with edge cases — always verify your queries with rows that have no matches.

Module Complete

These skills enable you to answer a category of questions that INNER JOIN cannot address—finding what's missing, creating complete reports, and reconciling data across sources.

Module Complete

5 / 5