Joins and NULL Semantics

Joins are the half of SQL interviews that people prepare. NULLs are the half that decides the outcome, because every NULL bug returns a plausible answer rather than an error.

Four joins, four row counts

SELECT 'inner' AS kind, count(*) FROM customers c JOIN orders o USING (customer_id)
UNION ALL
SELECT 'left',       count(*) FROM customers c LEFT JOIN orders o USING (customer_id)
UNION ALL
SELECT 'right',      count(*) FROM customers c RIGHT JOIN orders o USING (customer_id)
UNION ALL
SELECT 'full',       count(*) FROM customers c FULL JOIN orders o USING (customer_id)
UNION ALL
SELECT 'cross',      count(*) FROM customers c CROSS JOIN orders o;

$ duckdb interview.duckdb < joins.sql
┌─────────┬──────────────┐
│  kind   │ count_star() │
│ varchar │    int64     │
├─────────┼──────────────┤
│ inner   │            7 │
│ left    │            9 │
│ right   │            8 │
│ full    │           10 │
│ cross   │           48 │
└─────────┴──────────────┘

Every number is explainable, and being able to explain them is the point:

• inner 7 — 8 orders, but order 108 has a NULL customer_id and matches nothing.
• left 9 — those 7, plus Eve and Fay with NULL order columns.
• right 8 — all 8 orders, order 108 with NULL customer columns.
• full 10 — the 7 matches, plus Eve and Fay, plus order 108.
• cross 48 — 6 × 8, every pairing. Almost never what you want, and the accident behind most “why is my result 400 million rows” incidents.

The NULL join key

SELECT o.order_id, o.customer_id, c.name
FROM orders o LEFT JOIN customers c USING (customer_id)
ORDER BY o.order_id;

┌──────────┬─────────────┬─────────┐
│ order_id │ customer_id │  name   │
│  int32   │    int32    │ varchar │
├──────────┼─────────────┼─────────┤
│      101 │           1 │ Ana     │
│      102 │           1 │ Ana     │
│      103 │           2 │ Bo      │
│      104 │           3 │ Cy      │
│      105 │           1 │ Ana     │
│      106 │           2 │ Bo      │
│      107 │           4 │ Di      │
│      108 │        NULL │ NULL    │
└──────────┴─────────────┴─────────┘

NULL never equals NULL. Order 108 has a NULL customer_id and customer 6 exists — they do not match, and no join type will match them. NULL = NULL is UNKNOWN, not true.

SELECT NULL = NULL           AS eq,
       NULL <> NULL          AS neq,
       NULL IS NULL          AS is_null,
       NULL IS NOT DISTINCT FROM NULL AS null_safe;

┌─────────┬─────────┬─────────┬───────────┐
│   eq    │   neq   │ is_null │ null_safe │
│ boolean │ boolean │ boolean │  boolean  │
├─────────┼─────────┼─────────┼───────────┤
│ NULL    │ NULL    │ true    │ true      │
└─────────┴─────────┴─────────┴───────────┘

IS NOT DISTINCT FROM is the NULL-safe equality (MySQL spells it <=>). It is the right tool when NULL genuinely means “same unknown”, which is rare — usually a NULL join key is a data quality problem to surface, not to join through.

NOT IN against a NULL returns nothing

-- "customers who have never ordered"
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

┌─────────┐
│  name   │
│ varchar │
├─────────┤
│ 0 rows  │
└─────────┘

Zero rows. Eve and Fay have never ordered, and the query says nobody has. It returns no error and no warning.

The subquery contains a NULL (order 108). customer_id NOT IN (1,2,3,4,NULL) expands to:

customer_id <> 1 AND customer_id <> 2 AND ... AND customer_id <> NULL
                                                 ^^^^^^^^^^^^^^^^^^^^
                                                 UNKNOWN, never true

An AND chain containing UNKNOWN can be false or unknown, never true. Every row is filtered out.

Three fixes, and only two are good:

-- 1. NOT EXISTS — NULL-safe by construction
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- 2. LEFT JOIN / IS NULL — the classic anti-join
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- 3. NOT IN with an explicit guard — works, but you must remember it forever
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

┌─────────┐        ┌─────────┐        ┌─────────┐
│  name   │        │  name   │        │  name   │
├─────────┤        ├─────────┤        ├─────────┤
│ Eve     │        │ Eve     │        │ Eve     │
│ Fay     │        │ Fay     │        │ Fay     │
└─────────┘        └─────────┘        └─────────┘

“I default to NOT EXISTS for anti-joins. It is NULL-safe without me having to remember that it needs to be, and on most planners it optimises to the same anti-join as the LEFT JOIN version.”

Say that and the topic is closed. It is one of the two or three highest-yield facts in a SQL round.

Where the filter goes changes the join

-- WHERE: silently becomes an inner join
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'shipped'
ORDER BY c.name, o.order_id;

┌─────────┬──────────┐
│  name   │ order_id │
├─────────┼──────────┤
│ Ana     │      101 │
│ Ana     │      102 │
│ Ana     │      105 │
│ Bo      │      103 │
│ Di      │      107 │
└─────────┴──────────┘

Five rows. Cy, Eve and Fay are gone — Cy’s only order is cancelled, and Eve and Fay have NULL in o.status, which fails = 'shipped'.

-- ON: the LEFT JOIN is preserved
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'shipped'
ORDER BY c.name, o.order_id;

┌─────────┬──────────┐
│  name   │ order_id │
├─────────┼──────────┤
│ Ana     │      101 │
│ Ana     │      102 │
│ Ana     │      105 │
│ Bo      │      103 │
│ Cy      │     NULL │
│ Di      │      107 │
│ Eve     │     NULL │
│ Fay     │     NULL │
└─────────┴──────────┘

The ON clause decides what matches; WHERE filters what survives. For an INNER JOIN the two are equivalent. For a LEFT JOIN they are completely different queries, and the WHERE version is one of the most common silent bugs in production reporting.

The tell: any condition on a right-hand-table column in the WHERE clause of a LEFT JOIN is suspect. The exception is deliberate — WHERE o.order_id IS NULL is the anti-join, and it works precisely because it selects the unmatched rows.

Aggregates skip NULLs

SELECT
    count(*)                    AS rows_total,
    count(country)              AS country_not_null,
    count(DISTINCT country)     AS distinct_countries,
    avg(CASE WHEN country = 'UK' THEN 1 ELSE 0 END) AS uk_share
FROM customers;

┌────────────┬──────────────────┬────────────────────┬──────────┐
│ rows_total │ country_not_null │ distinct_countries │ uk_share │
│   int64    │      int64       │       int64        │  double  │
├────────────┼──────────────────┼────────────────────┼──────────┤
│          6 │                5 │                  3 │     0.33 │
└────────────┴──────────────────┴────────────────────┴──────────┘

count(*) is 6, count(country) is 5, and count(DISTINCT country) is 3 — NULL is not a distinct value.

The one that bites in real analysis is avg:

CREATE TEMP TABLE scores (v INTEGER);
INSERT INTO scores VALUES (10), (20), (NULL), (30);

SELECT avg(v)                       AS avg_skips_null,
       sum(v) / count(*)            AS avg_over_all_rows,
       avg(coalesce(v, 0))          AS avg_null_as_zero;

┌────────────────┬───────────────────┬──────────────────┐
│ avg_skips_null │ avg_over_all_rows │ avg_null_as_zero │
│     double     │      double       │      double      │
├────────────────┼───────────────────┼──────────────────┤
│           20.0 │              15.0 │             15.0 │
└────────────────┴───────────────────┴──────────────────┘

Three different numbers, all defensible, for “the average”. Which is correct depends on whether a missing score means “no data” or “zero”, and that is a question for the interviewer, not a decision to make silently.

Self-joins

-- pairs of customers from the same country
SELECT a.name AS first, b.name AS second, a.country
FROM customers a
JOIN customers b ON a.country = b.country AND a.customer_id < b.customer_id
ORDER BY a.country, first;

┌─────────┬─────────┬─────────┐
│  first  │ second  │ country │
├─────────┼─────────┼─────────┤
│ Ana     │ Cy      │ UK      │
│ Bo      │ Eve     │ US      │
└─────────┴─────────┴─────────┘

a.customer_id < b.customer_id does two jobs: it stops each row joining to itself, and it stops each pair appearing twice in both orders. Writing <> instead gives four rows — the classic self-join mistake.

Fay is absent, because her country is NULL and NULL never equals NULL.

Recognising it

QUESTION                                        JOIN
"per X, including those with none"              LEFT JOIN from X, count(child_col)
"X that have never done Y"                      NOT EXISTS, or LEFT JOIN + IS NULL
"X that have done Y"                            EXISTS, or an inner join + DISTINCT
"pairs / comparisons within one table"          self-join with a < b to dedupe
"all of both sides"                             FULL JOIN — rare, and usually a red flag
filter on the right table of a LEFT JOIN        it goes in ON, not WHERE
join key can be NULL                            surface it; do not join through it

Practice

0 rows

WHERE: 5 rows      ON: 8 rows

avg(v)=20.0   sum(v)/count(*)=15.0   avg(coalesce(v,0))=15.0

4 rows instead of 2 — every pair appears in both orders.

Next: GROUP BY and aggregates — what you may select alongside a group, and the difference between filtering rows and filtering groups.