Handling NULLs in Joins
You'll learn how NULL values behave in join conditions and affect your join results.
When you join tables, NULL values require special attention because they don't behave like regular data. Understanding this is crucial for writing correct queries and interpreting your results.
NULL in Join Conditions
Remember that NULL represents "unknown" or "missing" data. When SQL evaluates join conditions, NULL never equals NULL—not even to itself. This means:
-- These rows will NOT match in a join condition
Table A: id = NULL
Table B: id = NULL
-- NULL = NULL evaluates to UNKNOWN, not TRUE
If you're joining on a column that contains NULLs, those NULL rows won't match anything—even other NULLs. They'll simply be excluded from an INNER JOIN result.
NULLs in Outer Joins
Outer joins (LEFT, RIGHT, FULL) are designed to preserve unmatched rows. When a row from the preserved table has no match:
- The preserved table's columns show their actual values (including any NULLs in the data)
- The other table's columns are filled with NULL (representing "no matching row found")
This creates two types of NULLs in your result set:
- Data NULLs: NULLs that exist in your actual table data
- Join NULLs: NULLs added by SQL because no matching row was found
-- LEFT JOIN example
SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;
-- If a customer has no orders, order_id will be NULL (join NULL)
Key Takeaway: NULL values in join columns prevent matches (since NULL ≠ NULL), and outer joins introduce additional NULLs for unmatched rows—always use IS NULL to check for them, and be mindful of which NULLs are "real data" versus "no match found."