Outer Join Nullability Issues

In this lesson, you will learn how the NULL values created by an outer join can accidentally hide records when you apply filters in your WHERE clause.

The Problem: When Filters "Undo" the Join

When you perform a LEFT JOIN, you intentionally keep rows from your primary table even if there is no match in the secondary table. In those unmatched cases, SQL fills the columns from the secondary table with NULL.

The trap happens when you add a WHERE clause to filter those secondary columns. Because NULL is not equal to any value, your filter will often silently discard all the unmatched rows you just worked so hard to preserve.

A Practical Example

Imagine you have a list of Customers and their Orders. You want to see all customers, even those who haven't placed an order yet:

SELECT c.name, o.order_date
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customer_id
WHERE o.order_date > '2023-01-01';

In this query, any customer who has never placed an order will have a NULL in the order_date column. Because NULL > '2023-01-01' is neither true nor false (it is "unknown"), the database excludes those customers entirely. Your LEFT JOIN effectively turned back into an INNER JOIN because of the WHERE condition.

The Solution: Move the Logic

If you need to filter the joined table but want to keep the "unmatched" rows, you have two main options:

1. Move the condition to the ON clause: By placing the date constraint directly in the join condition, the database applies the filter before it decides whether to keep the row.
2. Account for NULLs: Use OR o.order_date IS NULL in your WHERE clause to explicitly include the records that failed the join.

Usually, moving the constraint into the ON clause is the cleanest approach, as it keeps your join logic self-contained.

Key Takeaway: Filters in a WHERE clause act as a guardrail that can inadvertently drop the NULL records generated by an outer join; always check if your filter criteria should live in the ON clause instead.