Subqueries & CTEs

Queries inside queries: scalar and correlated subqueries, IN and EXISTS, derived tables, WITH clauses, and recursive CTEs for trees and graphs.

Overview

A subquery is a SELECT written inside another statement. It lets you compute something first, such as an average or a list of ids, and then use it in the outer query. A CTE (common table expression) is the same idea with a name: WITH name AS (SELECT ...) defines a result you can read like a table in the query that follows.

Interviewers use these to test two things: whether you can break a question into steps, and whether you know the traps, above all NOT IN with a NULL and recursion that never ends.

The sample table

The table has six employees in two departments. manager_id points to another row, so the table also holds a small org chart. All the SQL on this page runs on PostgreSQL.

Where a subquery can go

What a subquery returns decides where it can be used.

Scalar — one value — SELECT list, WHERE, anywhere a value fits — salary > (SELECT AVG(salary) FROM employees)
List — one column, many rows — IN, NOT IN, = ANY — id IN (SELECT manager_id FROM ...)
Existence — true or false — EXISTS, NOT EXISTS — EXISTS (SELECT 1 FROM ... WHERE ...)
Table — rows and columns — FROM (a derived table) — FROM (SELECT dept, MAX(salary) ...) AS t

A scalar subquery must return at most one row. If it returns two, PostgreSQL stops with 'more than one row returned by a subquery used as an expression'. If it returns none, the value is NULL.

The company average is 100000, so two people qualify.

Ana — 150000
Ben — 120000

A derived table is a subquery in FROM. Give it an alias: the SQL standard requires one, and PostgreSQL only made it optional in version 16.

Eng — 150000
Sales — 100000

Correlated subqueries

A correlated subquery uses a column from the outer query, so its answer changes from row to row. Think of it as running once for each outer row. The planner may find a faster plan, but the result is always the same as that picture.

The Eng average is 120000 and the Sales average is 80000.

Ana — Eng — 150000
Dee — Sales — 100000

IN, EXISTS and the NULL trap

EXISTS asks only whether the subquery returns any row, so the select list does not matter; SELECT 1 is the usual way to write it. PostgreSQL runs the subquery only until it finds one row. IN compares a value with every value the subquery returns.

The trap is NOT IN. If the list contains a NULL and the value is not in it, the answer is unknown, not true, and WHERE drops the row.

3 IN (list) — no match, but a NULL is present — NULL, row dropped
3 NOT IN (list) — no match, but a NULL is present — NULL, row dropped
NOT EXISTS (row with value 3) — no such row — true, row kept

'Who manages nobody?' shows it. Ana's manager_id is NULL, so the list has a NULL, and NOT IN returns no rows.

This returns no rows. NOT EXISTS gives the right answer.

CTEs: naming the steps

A CTE moves a subquery to the top and gives it a name. The query reads top to bottom, and you can use the name more than once.

Ana — Eng — 150000 — 120000
Dee — Sales — 100000 — 80000

Whether a CTE is computed once or folded into the main query depends on the database. PostgreSQL 12 and later fold it in when it is not recursive, has no side effects and is used only once; otherwise it computes it once and reuses the result.

a plain CTE used once — folds it into the main query, so filters can reach inside
a plain CTE used twice or more — computes it once, then reads the stored result
AS MATERIALIZED (...) — always computes it once, separately
AS NOT MATERIALIZED (...) — folds it in even if used twice, as long as it is not recursive and has no side effects

Recursive CTEs

A recursive CTE walks a tree or a graph. It has two parts joined by UNION ALL: an anchor that picks the starting rows, and a recursive step that joins the table to the rows found so far. The step repeats on the newest rows until it finds nothing new.

Ana — 1 — Ana
Ben — 2 — Ana / Ben
Cai — 3 — Ana / Ben / Cai
Dee — 2 — Ana / Dee
Eli — 3 — Ana / Dee / Eli
Gus — 4 — Ana / Dee / Eli / Gus

To walk up instead of down, start from one person and join each manager: anchor on name = 'Gus' and join m.id = up.manager_id.

In a tree the recursion stops by itself at the leaves. In a graph with a loop it would run forever with UNION ALL. The guards differ by database.

UNION instead of UNION ALL — PostgreSQL drops rows already found, so a revisited node adds nothing; this fails if the row carries a column that keeps changing, such as a depth or a path
a depth counter — add WHERE depth < 10 to the recursive step
CYCLE col SET is_cycle USING path — PostgreSQL 14 and later mark a row that returns to a visited node and stop that branch
SQL Server — OPTION (MAXRECURSION n); the server-wide default is 100 levels, and 0 removes the limit
MySQL — cte_max_recursion_depth; the default is 1000

PostgreSQL itself has no default depth limit, so the guard is your job.

The links form a loop (1, 2, 3, back to 1). With UNION, node 1 is not added a second time, so the query ends.

What you will be able to do

Pick the right kind of subquery for a value, a list, an existence test or a table.
Explain why NOT IN returns nothing when the list holds a NULL, and fix it.
Rewrite a nested query as a readable chain of CTEs.
Write a recursive CTE for a hierarchy and guard it against loops.

When to use it

Comparing each row with a single computed value, such as the overall average (scalar subquery).
Comparing each row with its own group, such as its department average (correlated subquery or CTE).
Testing whether a related row exists without returning it (EXISTS, NOT EXISTS).
Breaking a long query into named, readable steps (WITH).
Walking a hierarchy or graph of unknown depth, such as an org chart or category tree (WITH RECURSIVE).

Interview questions

What is the difference between a correlated and an uncorrelated subquery?
When would you use a CTE instead of a subquery?
How do you query a hierarchy of unknown depth?

More Databases & SQL topics

Start free on Cariara · Coding problems · System design studio