Subqueries and CTEs

7 pages · ~5 min✓ Reviewed

Builds on Aggregates and GROUP BY. Next up: Window Functions.

SQLPage 1

Subqueries and CTEs: The Mental Model

1. What a Subquery Is

A subquery is a SELECT nested inside another statement
Inner result acts as a value, a list or a table
Scalar = exactly one row, one column; fits wherever an expression fits

sql

SELECT name, (SELECT MAX(salary) FROM emp) AS top
FROM emp;

scalar subquery in the SELECT list

2. Placement Decides Shape

Where	Takes
`SELECT` / `WHERE`	scalar or list
`FROM`	table
CTE	table, referenced many times

3. Derived Table vs CTE

Derived table: a subquery in FROM, named by an alias
CTE: WITH name AS (...), valid only for the one statement after it

sql

SELECT d.dept, d.a
FROM (SELECT dept, AVG(salary) a FROM emp GROUP BY dept) d;

derived table with alias d

Alias on a derived table

Required in most engines. PostgreSQL 16+ and SQLite do not require one.

SQLPage 2

IN, EXISTS and Correlated Subqueries

1. IN and EXISTS

IN compares a value against the list the subquery returns
WHERE dept_id IN (SELECT id FROM dept WHERE city='Pune')
EXISTS is true if the subquery yields at least one row
Selected columns are ignored, so SELECT 1 is conventional

2. Correlated Subqueries

Refers to a column of the outer query
Logically re-evaluated per outer row

sql

SELECT name, salary FROM emp e1
WHERE salary > (
  SELECT AVG(salary) FROM emp e2
  WHERE e2.dept_id = e1.dept_id);

e1 is the outer row: employees above their own dept average

3. Choosing and Anti-join

	IN	EXISTS
Works by	builds or probes a list	stops at first match
NULLs	tricky	NULL-safe
Best for	small static lists, readable	correlated checks

Anti-join: NOT EXISTS finds customers with no orders

sql

SELECT c.id FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.cust_id = c.id);

Prefer NOT EXISTS

A single NULL in a NOT IN list makes the whole filter return no rows. NOT EXISTS is safe.

SQLPage 3

CTEs and Recursive CTEs

1. CTE basics

sql

WITH high AS (SELECT * FROM emp WHERE salary > 50000),
     b AS (SELECT * FROM dept)
SELECT * FROM high;

chain several with commas, one WITH

Prefer over a nested derived table: name each step, reuse it many times
No guarantee of materialization: the engine decides

2. Recursive CTE

Anchorstart rows
UNION ALL
Recursive memberreads the CTE
Stopno rows returned

sql

WITH RECURSIVE t AS (
  SELECT id, boss_id, 1 AS lvl FROM emp WHERE boss_id IS NULL
  UNION ALL
  SELECT e.id, e.boss_id, t.lvl+1
  FROM emp e JOIN t ON e.boss_id = t.id
  WHERE t.lvl < 10
)
SELECT * FROM t;

org chart with a depth cap

3. Cycles

Cyclic data loops forever
Fix: depth cap or the engine's cycle limit
UNION ALL does not stop cycles

SQLPage 4

UNION vs UNION ALL and Choosing a Form

1. UNION vs UNION ALL

	UNION	UNION ALL
Duplicates	removed	kept
Extra step	sort or hash	none
Speed	slower	faster
Use when	you need distinct rows	duplicates impossible or wanted

2. Rules for Combining

Same column count in both SELECTs
Types compatible by position
Column names come from the first SELECT
One ORDER BY for the whole result, at the very end

sql

SELECT name, city FROM emp
UNION ALL
SELECT cname, town FROM customer
ORDER BY name;

result columns are name, city; ORDER BY goes last

3. Choosing a Form

You need	Use
A single value	scalar subquery
A membership test	`IN`
An existence test	`EXISTS`
Reused or readable steps	CTE
A hierarchy	recursive CTE

Default

Reach for UNION ALL first. Use UNION only when you must drop duplicates.

SQLPage 5

Common Mistakes with Subqueries and Set Operators

1. Silent wrong results

NOT IN with one NULL in the list returns zero rows (x <> NULL is unknown)
Fix: NOT EXISTS, or filter NULLs out of the subquery
UNION where UNION ALL fits: needless sort, drops real duplicates

sql

SELECT c.id FROM cust c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.cust_id = c.id
);

NULL-safe anti-join

2. Errors

Mistake	Result	Fix
Scalar subquery returns >1 row	runtime error	unique key, `MAX()`, `LIMIT 1`
`FROM (SELECT ...)` without alias	fails in most engines (not PG 16+, SQLite)	`FROM (SELECT ...) d`

3. Endless recursion

Recursive CTE form	Cyclic data
`UNION ALL`, no cap	loops forever
`UNION`, identical rows repeat	can stop a simple cycle
`UNION` + depth counter	still loops: every row is distinct
Depth cap, path check, PG 14+ `CYCLE`	stops

UNION ALL is not a loop guard

It is chosen for speed (SQL Server requires it). Always add WHERE t.lvl < 10.

SQLPage 6

Subqueries, CTEs and Set Operators Cheat Sheet

1. Subquery and CTE forms

Form	Gives
`(SELECT MAX(x) FROM t)`	1 row, 1 col: SELECT, WHERE, HAVING
`col IN (SELECT ...)`	a list
`EXISTS (SELECT 1 ... WHERE i.k = o.k)`	true/false per outer row
`FROM (SELECT ...) d`	table; alias in most engines
`WITH n AS (...) SELECT ... FROM n`	named, reusable table

2. Recursive skeleton

Anchor UNION ALL step; stops when the step returns no rows
UNION ALL won't stop cycles: add a depth cap
SQL Server omits RECURSIVE

sql

WITH RECURSIVE r AS (
  SELECT id, 1 AS lvl FROM emp WHERE boss_id IS NULL
  UNION ALL
  SELECT e.id, lvl+1 FROM emp e JOIN r ON e.boss_id=r.id WHERE lvl<10
) SELECT * FROM r;

3. Set operators

	UNION	UNION ALL
Duplicates	removed	kept
Speed	slower (sort/hash)	faster

Same column count, compatible types by position
Names from first SELECT; one final ORDER BY

Prefer NOT EXISTS

NOT IN with a NULL in the list returns no rows.

SQLPage 7

Check yourself

orders.cust_id contains one NULL. What does SELECT name FROM customers WHERE id NOT IN (SELECT cust_id FROM orders) return?

Zero rows.
id <> NULL is unknown, so no row passes.
Fix: use NOT EXISTS.

A recursive CTE uses UNION ALL and lvl+1 on data with a cycle. What happens?

It loops forever.
Fix: add WHERE t.lvl < 10.
UNION would not help, because lvl makes every row distinct.

Table a holds 1, 1 and table b holds 1. How many rows do UNION and UNION ALL return?

UNION: 1 row, duplicates removed.
UNION ALL: 3 rows, everything kept.