Bridge · From syntax to engineering · 25 MIN

Find missing relationships safely with NOT EXISTS

Ask whether a related row exists instead of multiplying rows with a join.

A correlated NOT EXISTS checks each customer for any matching order. It does not care how many orders match. NOT IN behaves differently when its subquery contains NULL: comparisons can become unknown and exclude every row. The correlated predicate makes the relationship explicit. For a large database, an index on orders(customer_id) may help the lookup; inspect the real plan. This exercise runs in SQLite, while production plan choices depend on the engine and its statistics.

Write a small contract first, then test how the implementation behaves at its boundaries.

Read the example

SELECT id FROM customers ORDER BY id;

[[1],[2],[3]]

Your challenge

Return id and name for customers who have no matching order. Ignore orders with a NULL customer_id. Order the result by customer id.

Solution cost: Plan dependent: possible O(c log o) indexed probes; O(c × o) without a useful access path. time · Plan dependent; output O(k). space

Common trap

NOT IN (SELECT customer_id FROM orders) can fail when any returned customer_id is NULL.

Further reading: SQLite expressions and EXISTS