In an INNER JOIN on a.dept_id = b.dept_id, what happens to rows where dept_id is NULL?
answer
- equality has three possible outcomes
- unknown is not the same as equal
- the predicate must come out TRUE
- two NULLs are two different unknowns
- UNKNOWN is discarded just like FALSE
basics
~20 sThey disappear from the result. NULL = NULL evaluates to UNKNOWN rather than TRUE, and a join keeps only pairs whose predicate is TRUE, so a NULL key matches nothing — not even a NULL key on the other side.
solid answer
~50 sA join predicate is evaluated for each candidate row pair, and only pairs where it evaluates to `TRUE` survive. NULL is a marker for *unknown*, so `a.dept_id = b.dept_id` with a NULL on either side yields `UNKNOWN`, which is not `TRUE`. An employee row with a NULL `dept_id` therefore joins to no department, a department row with a NULL `dept_id` joins to no employee, and the two never find each other. With an INNER JOIN both rows simply vanish — no error, no warning, just a smaller result. With a `LEFT JOIN` the left row survives, but it survives as an *unmatched* row: every right-hand column comes back NULL-extended. If you actually want NULL to match NULL you have to ask for it explicitly, with `IS NOT DISTINCT FROM` or a sentinel value; plain equality will never do it.
code
sql · 7 linesINSERT INTO employees VALUES (1,'Ada',10),(2,'Bo',20),(3,'Cy',NULL);
INSERT INTO departments VALUES (10,'Eng'),(20,'Ops'),(NULL,'Unassigned');
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
-- 2 rows: Ada/Eng and Bo/Ops. Cy never meets 'Unassigned'.go deeper
Remember the fact and say it plainly: a row with a NULL join key matches nothing under =, so an INNER JOIN silently drops it. Be able to point out that no error is raised.
Explain the mechanism: comparisons return TRUE, FALSE or UNKNOWN, the ON clause admits only TRUE, and a conjunction of a matching column with an UNKNOWN one is still UNKNOWN. Walk through what each join type then does with the unmatched row.
Show how you catch this in production: compare inner and outer join counts, count NULL keys before trusting a total, and treat a nullable foreign key as a known reconciliation hazard rather than waiting for a user to report a missing record.
Own the modelling call. Decide whether a nullable foreign key should exist at all, or whether an explicit unknown-member row plus a NOT NULL column is the cheaper contract for every query author who will ever touch the table.
## 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.
- Does a LEFT JOIN rescue the rows whose left-side key is NULL?It keeps them, but not by matching them. The left row is preserved because the left side is the preserved side; the predicate still evaluated to UNKNOWN, so the row comes back with every right-hand column NULL-extended. It is not paired with right rows that also have NULL keys. Rows with NULL keys on the right side still disappear entirely.
- What happens with a two-column join key when only one column is NULL?The pair is still rejected. The predicate is a conjunction, and TRUE AND UNKNOWN is UNKNOWN, which the join filters out just like FALSE. So `ON a.region = b.region AND a.sku = b.sku` drops the pair when sku is NULL on one side, even though region matched exactly.
- How would you measure how many rows a nullable join key is silently dropping?Count them directly with `SELECT COUNT(*) FROM employees WHERE dept_id IS NULL`, and run the same join once as INNER and once as LEFT; the difference in row counts is the set the equality predicate rejected. Doing that before trusting a total is a cheap habit on any query over a nullable foreign key.
saying these in an interview costs you the question
- Says NULL = NULL is TRUE, so the rows match
- Thinks two NULL keys join to each other
- Believes LEFT JOIN makes NULL keys match right rows
- Claims the engine raises an error on NULL join keys
- Writes ON a.dept_id = NULL to catch those rows