SQL Join Interview Exercises: Preservation, Absence, and Matching

Four SQL join exercises with traps and exact answers

These PostgreSQL 16 exercises test join decisions rather than ask you to memorize definitions. Each block is self-contained and read-only. Before running an answer, state the result population, key, and expected row count. For the underlying join types, start with one-dataset join explanations.

Practice rules

Use identities rather than names, assume only the uniqueness stated by the fixture, and preserve NULL when it means unknown. A question asking whether a match exists is different from a question asking for every matching detail row. Adding DISTINCT at the end is not a substitute for choosing the right relationship.

Exercise 1: preserve every department

The output must contain departments with no employees. Which table must be preserved by RIGHT JOIN? Two employees belong to Data and Support is empty. The right-hand input below is departments, so the result includes a null-extended employee for Support.

WITH employees(id,department_id) AS (VALUES(1,10),(2,10)),
departments(id,name) AS (VALUES(10,'Data'),(20,'Support'))
SELECT d.id,d.name,e.id AS employee_id
FROM employees e RIGHT JOIN departments d ON e.department_id=d.id
ORDER BY d.id,e.id;

id | name | employee_id
10 | Data | 1
10 | Data | 2
20 | Support | NULL

Writing departments RIGHT JOIN employees would preserve employees instead and fail the request. Reversing both operands and using LEFT JOIN is a valid equivalent. If the output were one row per department, aggregate with COUNT(e.id); COUNT(*) would incorrectly report one employee for Support’s preserved row.

Exercise 2: find customers with no paid order

Customer 1 has one cancelled and one paid order. Customer 2 has only a cancelled order. Customer 3 has no order. A fourth order has a NULL customer ID. Return customers 2 and 3, once each. Testing for no order of any status would incorrectly exclude customer 2.

WITH customers(id) AS (VALUES(1),(2),(3)),
orders(id,customer_id,status) AS (
 VALUES(10,1,'cancelled'),(11,1,'paid'),(12,2,'cancelled'),(13,NULL,'paid')
)
SELECT c.id FROM customers c
WHERE NOT EXISTS(SELECT 1 FROM orders o
 WHERE o.customer_id=c.id AND o.status='paid') ORDER BY c.id;

id
2
3

NOT EXISTS asks the desired existence question without multiplying customer rows. A LEFT JOIN alternative must put status = paid inside ON and test a non-NULL order key for no match. NOT IN over nullable customer_id is dangerous here: the NULL can make otherwise unmatched comparisons unknown. The optimizer can choose an efficient anti-join; syntax alone is not a speed guarantee.

Exercise 3: reconcile two complete snapshots

Both inputs have a unique non-NULL SKU. Snapshot A has x=10 and y=NULL; snapshot B has x=12, y=NULL, and z=5. The NULL quantities are unknown. Report changed quantities and rows absent from either snapshot without mislabeling two unknown quantities as a numeric mismatch.

WITH a(sku,qty) AS (VALUES('x',10),('y',NULL::integer)),
b(sku,qty) AS (VALUES('x',12),('y',NULL::integer),('z',5))
SELECT COALESCE(a.sku,b.sku) AS sku,a.qty AS old_qty,b.qty AS new_qty,
 CASE WHEN a.sku IS NULL THEN 'new'
      WHEN b.sku IS NULL THEN 'removed' ELSE 'changed' END AS change
FROM a FULL OUTER JOIN b ON a.sku=b.sku
WHERE a.sku IS NULL OR b.sku IS NULL OR a.qty IS DISTINCT FROM b.qty
ORDER BY sku;

sku | old_qty | new_qty | change
x | 10 | 12 | changed
z | NULL | 5 | new

The presence test uses SKU because it is non-NULL for real source rows; testing qty IS NULL would confuse an unknown quantity with an absent row. IS DISTINCT FROM is a NULL-aware comparison in PostgreSQL. If either snapshot has repeated SKUs, define the grain or resolve versions before reconciliation. FULL OUTER JOIN does not deduplicate either side.

Exercise 4: create a complete, bounded reporting grid

Two stores and two days define four report rows. The complete source has two sales records for store 1 on January 1 and one for store 2 on January 2. Aggregate the facts first, then join them to the grid. The zero policy is justified only because missing facts mean no sales in this example.

WITH stores(id) AS (VALUES(1),(2)),
days(day) AS (VALUES(DATE '2026-01-01'),(DATE '2026-01-02')),
sales(store_id,day,amount) AS (
 VALUES(1,DATE '2026-01-01',10),(1,DATE '2026-01-01',5),(2,DATE '2026-01-02',7)
), daily AS (
 SELECT store_id,day,SUM(amount) AS amount FROM sales GROUP BY store_id,day
)
SELECT s.id,d.day,COALESCE(f.amount,0) AS amount
FROM stores s CROSS JOIN days d LEFT JOIN daily f ON f.store_id=s.id AND f.day=d.day
ORDER BY s.id,d.day;

id | day | amount
1 | 2026-01-01 | 15
1 | 2026-01-02 | 0
2 | 2026-01-01 | 0
2 | 2026-01-02 | 7

The report has exactly four rows and total amount 22. A broad store-by-all-history grid can be enormous, so bound dates and population before crossing them. Partial ingestion should be marked incomplete rather than displayed as observed zero sales.

Assess the answer

A strong explanation identifies the preserved input, cardinality, matching predicate, and treatment of missing values. For each exercise, change one adversarial row and predict the result: an empty department, a NULL order customer, a removed SKU, or another same-day sale. Then check row count and totals. These are correctness tests; performance needs a representative plan and workload.

FAQ

Do these examples run in every engine? They target PostgreSQL 16; FULL OUTER JOIN and NULL-aware comparison syntax vary. Can an index repair the wrong preserved side? No. What should I read next? Multi-table joins and composite keys. Primary references: joins, EXISTS, and NULL-aware comparisons.