In an INNER JOIN on a.dept_id = b.dept_id, what happens to rows where dept_id is NULL?

## The rule in one line A join predicate admits a pair of rows only when it evaluates to `TRUE`. The comparison `x = y` with a NULL on either side evaluates to `UNKNOWN`, so a row whose join key is NULL matches nothing on the other side — including a row whose join key is also NULL. ## Why equality cannot see NULL NULL is not a value; it is a marker meaning "no value is recorded here". Asking whether one unrecorded thing equals another unrecorded thing has no defensible answer, so SQL's comparison operators return a third truth value, `UNKNOWN`, whenever an operand is NULL. The `ON` clause behaves like `WHERE` in this respect: it is a filter that lets `TRUE` through and rejects both `FALSE` and `UNKNOWN`. That is also why `= NULL` never matches anything and why the language provides a separate `IS NULL` predicate for asking the only question that has an answer: "is this marker present?" ## What each join type does with it The comparison itself never changes. What changes between join types is only what happens to rows that failed to match: - **INNER JOIN** — a row with a NULL key on either side produces no output row at all. - **LEFT OUTER JOIN** — the left row is preserved because the left side is the preserved side, but it is preserved as an unmatched row: all right-hand columns are NULL-extended. It is *not* paired with right rows that happen to have NULL keys. A right row with a NULL key still disappears. - **RIGHT OUTER JOIN** — the mirror image. - **FULL OUTER JOIN** — a NULL-keyed row on each side appears as two separate unmatched rows, never as one matched pair. - **CROSS JOIN** — has no predicate, so NULL keys are irrelevant; every pair is produced. ## Composite keys make it worse With `ON a.region = b.region AND a.sku = b.sku`, a NULL in `sku` on one side makes that conjunct `UNKNOWN`. In three-valued logic `TRUE AND UNKNOWN` is `UNKNOWN`, so the whole predicate is `UNKNOWN` and the pair is rejected even though `region` matched perfectly. One nullable column in a multi-column key is enough to lose the row. ## Worked example ```sql CREATE TABLE employees (emp_id INT, name VARCHAR(50), dept_id INT); CREATE TABLE departments (dept_id INT, dept_name VARCHAR(50)); INSERT INTO employees VALUES (1,'Ada',10),(2,'Bo',20),(3,'Cy',NULL),(4,'Di',NULL); INSERT INTO departments VALUES (10,'Eng'),(20,'Ops'),(NULL,'Unassigned'); SELECT COUNT(*) FROM employees e JOIN departments d ON e.dept_id = d.dept_id; -- 2: Ada and Bo. Cy and Di match nothing, not even the 'Unassigned' row. ``` Even though a human reader sees an "Unassigned" department sitting right there with a NULL key, the engine will not connect it to Cy and Di. Two NULLs are two separate unknowns, not one shared value. ## How the bug actually shows up Nothing errors. The query looks correct in review and returns plausible data. The symptoms are indirect: a report total that is quietly lower than the source system's, a dashboard whose row count drops the day someone starts leaving a foreign key unpopulated, a reconciliation that always misses the same handful of records. The cheapest diagnostic is to count the offenders directly — `SELECT COUNT(*) FROM employees WHERE dept_id IS NULL` — and to compare the row count of the inner join against the same query written as a `LEFT JOIN`. A gap between the two is precisely the set of rows the equality predicate rejected. ## What to do about it Pick the fix that matches what NULL actually means in your data: - If NULL means "not applicable yet" and those rows still belong in the output, use an outer join and handle the NULL-extended columns deliberately. - If NULL genuinely should match NULL — typically when you are reconciling two extracts with the same optional attribute — say so with `ON a.k IS NOT DISTINCT FROM b.k`, which returns `TRUE` for two NULLs and `FALSE` when only one side is NULL. - Best of all, remove the ambiguity from the model: declare the column `NOT NULL` and add an explicit "unknown" row to the referenced table. Then ordinary equality does exactly the right thing and no reader has to remember any of this. ## The one sentence to keep Equality in a join predicate is a test for *known sameness*; NULL is the absence of knowledge, so it never satisfies that test on either side of any join.