Subqueries & CTEs

Scalar cardinality, correlated vs uncorrelated, EXISTS vs IN, CTE fencing, recursive CTEs.

• ★Scalar subquery returning >1 row

  A scalar subquery that returns more than one row is a runtime error data volume decides — works in dev, dies in prod.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE items(id INT, sku VARCHAR); INSERT INTO items VALUES (1,'A'),(2,'B'); CREATE TABLE prices(sku VARCHAR, cents INT); INSERT INTO prices VALUES ('A',100),('B',200);

  The trap

  SELECT id, (SELECT cents FROM prices WHERE prices.sku = items.sku) AS price FROM items ORDER BY id;

  1	100
  2	200

  The fix

  SELECT i.id, p.cents AS price FROM items i JOIN prices p ON p.sku = i.sku ORDER BY i.id;

  1	100
  2	200

  It reads correctly today — but the day a SKU gets a second price row, the scalar subquery errors in production; a join on the unique key stays safe.

• Correlated vs uncorrelated

  A correlated subquery re-executes per outer row; EXISTS/IN differ in NULL behavior and cost.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE orders(cust VARCHAR, amt INT); INSERT INTO orders VALUES ('A',10),('A',30),('B',5),('B',15);

  The trap

  SELECT cust, amt FROM orders WHERE amt > (SELECT AVG(amt) FROM orders) ORDER BY cust, amt;

  A

  30

  The fix

  SELECT cust, amt FROM orders o WHERE amt > (SELECT AVG(amt) FROM orders o2 WHERE o2.cust = o.cust) ORDER BY cust, amt;

  A	30
  B	15

  The 'orders above their customer's average' report used a global average — the uncorrelated subquery computes AVG once over everything, so it missed B/15 (above B's own average but below the global); the correlated form asks per customer.

• CTE materialization / fencing

  Whether a CTE is inlined or materialized is engine-dependent — readability isn't free (bridge to P17).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE mat(part VARCHAR, cost INT); INSERT INTO mat VALUES ('beam',40),('rope',5),('beam',40);

  The trap

  SELECT (SELECT SUM(cost) FROM mat WHERE part='beam') AS beams, (SELECT SUM(cost) FROM mat WHERE part='rope') AS ropes;

  80

  5

  The fix

  WITH totals AS (SELECT part, SUM(cost) AS c FROM mat GROUP BY part) SELECT (SELECT c FROM totals WHERE part='beam') AS beams, (SELECT c FROM totals WHERE part='rope') AS ropes;

  80

  5

  Both return the same totals, but the copy-pasted subquery repeats the logic — a CTE names the step once; whether the engine inlines it (runs twice) or materializes it (once) is engine-dependent, decided by the plan.

• Recursive CTE basics

  A recursive CTE needs an anchor, a recursive term, and a termination — org charts, paths.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE keys(key_id VARCHAR, opens_lock VARCHAR, sub_of VARCHAR); INSERT INTO keys VALUES ('GRAND','L0',NULL),('A','L1','GRAND'),('B','L2','GRAND'),('A1','L3','A');

  The trap

  SELECT key_id, opens_lock FROM keys WHERE sub_of = 'GRAND' ORDER BY key_id;

  A	L1
  B	L2

  The fix

  WITH RECURSIVE walk(key_id, opens_lock, depth) AS (SELECT key_id, opens_lock, 0 FROM keys WHERE key_id = 'GRAND' UNION ALL SELECT k.key_id, k.opens_lock, w.depth + 1 FROM keys k JOIN walk w ON k.sub_of = w.key_id WHERE w.depth < 20) SELECT key_id, opens_lock, depth FROM walk ORDER BY depth, key_id;

  GRAND	L0	0
  A	L1	1
  B	L2	1
  A1	L3	2

  The one-level query found only GRAND's direct keys and missed the sub-keys beneath them — a recursive CTE walks the whole hierarchy, one level per step, carrying a depth.

• Scalar subquery is a left join

  A scalar subquery in SELECT behaves like a LEFT join (NULL on no match); an INNER JOIN drops the row — 'same query, rewritten for speed' silently loses entities.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE blends(blend VARCHAR); INSERT INTO blends VALUES ('Curry'),('Garam'); CREATE TABLE batches(blend VARCHAR, batch_no INT); INSERT INTO batches VALUES ('Curry',7),('Curry',9);

  The trap

  SELECT b.blend, MAX(ba.batch_no) AS latest FROM blends b JOIN batches ba ON ba.blend = b.blend GROUP BY b.blend ORDER BY b.blend;

  Curry

  9

  The fix

  SELECT b.blend, MAX(ba.batch_no) AS latest FROM blends b LEFT JOIN batches ba ON ba.blend = b.blend GROUP BY b.blend ORDER BY b.blend;

  Curry	9
  Garam	NULL

  The rewrite lost the new blend with no batches — a scalar subquery in SELECT is left-join-shaped and reads NULL, but the INNER JOIN rewrite drops the entity.

• EXISTS never evaluates its SELECT list

  EXISTS tests for rows, never values — its SELECT list is never evaluated, so SELECT 1, SELECT *, and even SELECT 1/0 are identical.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE members(m VARCHAR); INSERT INTO members VALUES ('m1'),('m2'); CREATE TABLE badges(m VARCHAR); INSERT INTO badges VALUES ('m1');

  The trap

  SELECT m FROM members WHERE EXISTS (SELECT 1/0 FROM badges b WHERE b.m = members.m) ORDER BY m;

  m1

  The fix

  SELECT m FROM members WHERE EXISTS (SELECT 1 FROM badges b WHERE b.m = members.m) ORDER BY m;

  m1

  The feared SELECT 1/0 inside EXISTS returns the same members and never errors — EXISTS tests for rows, so its SELECT list is never evaluated; SELECT 1, SELECT *, and SELECT 1/0 are identical, and the division never runs.