Cardinality — Learn Relationships with EXISTS, LATERAL, and Recursive CTEs in Practice

QUESTION 1

Semi-Join with EXISTS — Check for ‘has activity’ without increasing rows with JOIN

EXISTSSemi-JoinCorrelated subqueryRow-count preservation

Background ▼

When retrieving customers who have placed an order, an INNER JOIN between customers and orders duplicates a customer once for each order because of the 1:N relationship. You then need DISTINCT to remove duplicates, which adds waste and creates opportunities for bugs.

EXISTS (a semi-join) checks only whether at least one matching row exists in the right table. It filters the left table without increasing its rows or bringing in columns from the right. This is the correct existence check when you need to preserve cardinality.

-- INNER JOIN: customer rows are duplicated by order count → DISTINCT is needed
FROM customers c JOIN orders o ON c.customer_id = o.customer_id  -- Suzuki expands to 3 rows

-- EXISTS (semi-join): one matching order is enough, so the row stays single and no columns are added
FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)

Why SELECT 1: EXISTS asks only whether a row exists, so the selected expression can be anything; 1 is conventional. Evaluation often stops as soon as the first row is found (short-circuit evaluation), so EXISTS is frequently lighter than a join.

Problem ▼

Using customers and orders, retrieve customers with order history (customer_id, name). Use EXISTS and do not duplicate rows.

Tables used

▸ customers

customer_id	name
1	Tanaka
2	Sato
3	Yamada
4	Suzuki

▸ orders

order_id	customer_id	amount
101	1	5000
102	1	3000
103	2	8000
104	4	2000
105	4	4000
106	4	1000

Note: a direct INNER JOIN expands Suzuki (3 orders) to 3 rows. EXISTS is the correct approach; do not rely on DISTINCT.

Expected Output ▼

customer_id	name
1	Tanaka
2	Sato
4	Suzuki

Model Answer ▼

-- [Row-expanding version] INNER JOIN duplicates Suzuki 3 times, so DISTINCT is required
-- SELECT DISTINCT c.customer_id, c.name FROM customers c JOIN orders o ON ...

SELECT
  c.customer_id,
  c.name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id  -- Correlated: look only for orders tied to the outer customer
)
ORDER BY c.customer_id;

/*
  Execution order:
  1. FROM customers AS c           → Make all 4 customers evaluation targets
  2. WHERE EXISTS (...)            → Run the correlated subquery for each customer
  3. Yamada (id=3) has 0 matches   → Exclude with FALSE
  4. SELECT c.customer_id, c.name  → Project each customer at most once
  5. ORDER BY c.customer_id        → Output in ascending order
  */

Explanation (table transitions & key points) ▼

SELECT
  c.customer_id,
  c.name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

LEGEND ▼

Rows read / loaded

① Left table — customers ▼

FROM customers AS cThese are the 4 customers to evaluate. We check each row once for the existence of an order. EXISTS only filters these 4 rows; it neither increases nor decreases them before the predicate is applied.

1 / 6

customer_id	name
1	Tanaka
2	Sato
3	Yamada
4	Suzuki

4 customer rows (EXISTS does not expand these rows)

LEARNING POINTS

SEMI JOIN

EXISTS = semi-join — filter the left side by right-side matches without adding rows

Check existence while preserving cardinality
customers (N rows) WHERE EXISTS(orders) → filter with the same left-side row count

Why a semi-join preserves cardinality: a normal JOIN combines every matching right-side row, effectively multiplying the left row. EXISTS returns only the Boolean fact that at least one match exists, so one left row remains one row no matter how many matches there are. Because right-side columns are not brought into the result, no expansion occurs. That is why it is called a semi-join.

Choosing between EXISTS and IN: “customers with activity” can also be written as c.customer_id IN (SELECT customer_id FROM orders). Many databases optimize the two forms equivalently, but EXISTS is safer and more expressive when the subquery may contain NULL or when correlating on multiple columns (the NULL trap is covered in the next question).

ANTI-PATTERNS

Using JOIN + DISTINCT for an existence check: SELECT DISTINCT c.* FROM customers c JOIN orders o ... produces the right visible result, but it expands rows and then removes duplicates. As soon as you add an orders column to SELECT, DISTINCT no longer prevents the duplicates. If the goal is existence, write EXISTS from the start.

Counting with COUNT(*) inside EXISTS: WHERE (SELECT COUNT(*) FROM orders o WHERE ...) > 0 works, but it counts every matching row instead of benefiting from short-circuit evaluation. Use EXISTS when you need only yes/no; use COUNT only when the count itself is required.

FIELD NOTES

Semi-joins are among the most common filters in practice: show only products with reviews, extract active users who logged in during the past 90 days, or list only warehouses with stock. In each case, a parent is filtered by whether a matching child row exists. Using EXISTS without expanding rows prevents accidental double-counting in later aggregation. When you see WHERE EXISTS, immediately read it as “filter without increasing rows.”

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 2

Anti-Join with NOT EXISTS and the NOT IN NULL Trap — Customers with no orders

NOT EXISTSAnti-JoinNOT INThree-valued NULL logic

Background ▼

The reverse of the previous question is finding customers who have never placed an order: an anti-join. There are three common ways to write it.

① NOT EXISTS (correlated and NULL-safe), ② LEFT JOIN ... WHERE right_key IS NULL (keep rows with no match as NULL rows), and ③ NOT IN (subquery). The third form has a famous trap: if the target column contains even one NULL, the result is always 0 rows.

-- ✗ NOT IN: one NULL in the subquery makes the result empty
WHERE c.customer_id NOT IN (SELECT customer_id FROM orders)   -- NULL in orders → 0 rows

-- ✓ NOT EXISTS: correlated per-customer checks remain safe with NULL
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)

Why NOT IN is destroyed by NULL: 2 NOT IN (1, 4, NULL) becomes 2<>1 AND 2<>4 AND 2<>NULL. The final comparison, 2<>NULL, is UNKNOWN; once UNKNOWN enters an AND expression, the whole expression is UNKNOWN. SQL returns only TRUE rows, so no customer remains.

Problem ▼

Using customers and orders, retrieve customers who have never placed an order (customer_id, name). Use a NULL-safe NOT EXISTS approach.

Tables used

▸ customers

customer_id	name
1	Tanaka
2	Sato
3	Yamada
4	Suzuki

▸ orders

order_id	customer_id	product
101	1	Product A
102	1	Product B
103	4	Product C
104	NULL	Product D (guest purchase)

Note: orders contains a guest purchase with customer_id = NULL. Writing NOT IN (SELECT customer_id FROM orders) would return 0 rows.

Expected Output ▼

customer_id	name
2	Sato
3	Yamada

Model Answer ▼

-- ✗ NOT IN: orders.customer_id contains NULL, so the result is always empty (trap)
-- WHERE c.customer_id NOT IN (SELECT customer_id FROM orders)

SELECT
  c.customer_id,
  c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id  -- Correlated: confirm that this customer has no order
)
ORDER BY c.customer_id;

/*
  Execution order:
  1. FROM customers AS c           → Evaluate customers one row at a time
  2. WHERE NOT EXISTS (...)        → Keep only customers with zero orders
  3. SELECT c.customer_id, c.name  → Project the 2 columns
  4. ORDER BY c.customer_id        → Sort and output
  */

Explanation (table transitions & key points) ▼

SELECT
  c.customer_id,
  c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

LEGEND ▼

Rows read / loaded

① Left table — customers ▼

FROM customers AS cThese are the 4 customers to evaluate. We keep customers with no orders at all. Like EXISTS, an anti-join never increases the number of left-side rows.

1 / 5

customer_id	name
1	Tanaka
2	Sato
3	Yamada
4	Suzuki

4 customer rows

LEARNING POINTS

ANTI-JOIN & NULL

NOT EXISTS = anti-join — keep only left rows with no right-side match

NOT IN is destroyed by one NULL; NOT EXISTS is NULL-safe
2 <> NULL = UNKNOWN → the AND expression is UNKNOWN → 0 rows

Three anti-join forms and their equivalence: ① NOT EXISTS, ② LEFT JOIN orders o ON ... WHERE o.order_id IS NULL, and ③ NOT IN all aim to keep left rows with no match. ① and ② are NULL-safe and produce the same result, but ③ breaks when the subquery contains NULL. When in doubt, NOT EXISTS is the safest and clearest choice.

Three-valued logic (TRUE / FALSE / UNKNOWN): a SQL comparison returns “unknown” when NULL is involved. Both x = NULL and x <> NULL are UNKNOWN. WHERE keeps only TRUE rows, so UNKNOWN behaves like FALSE in the result. The AND chain in NOT IN is wiped out by this UNKNOWN propagation. Always check whether NULL can appear when writing an anti-join.

ANTI-PATTERNS

Using NOT IN against a nullable column: this is the biggest trap. One NULL in the subquery column makes the result empty, and no error is raised, so the bug is easy to miss. If you must use NOT IN, add WHERE customer_id IS NOT NULL inside the subquery, or replace it with NOT EXISTS.

Checking IS NULL on a nullable non-key column after LEFT JOIN: with LEFT JOIN ... WHERE o.product IS NULL, a legitimate matched row whose product was originally NULL would also be selected. For an anti-join, always check the join key (or primary key) with IS NULL to identify a missing match.

FIELD NOTES

Anti-joins are common for detecting churned members with no recent orders, respondents who have not submitted a survey, or orphaned child records whose parent is missing. Production subqueries often contain NULL from external-system integrations, so remembering NOT EXISTS as the default for anti-joins helps prevent incidents.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 3

Conditional Aggregation (FILTER / CASE) — Aggregate multiple metrics in one row without repeated JOINs

Conditional aggregationFILTER / CASEPivotN:1 aggregation

Background ▼

Suppose we want one row per customer containing the counts of completed, cancelled, and pending orders plus the completed amount. Joining orders three times by status, or writing three subqueries, makes the query complex and slow and introduces the risk of join expansion.

With conditional aggregation, one GROUP BY folds N rows into 1 row while routing each condition into a separate output column (a pivot). PostgreSQL's aggregate_function FILTER (WHERE condition) is the most readable form; for portability, the equivalent is SUM(CASE WHEN ...).

-- FILTER: specify which rows an aggregate should include after the function (standard SQL)
COUNT(*) FILTER (WHERE status = 'completed') AS completed_count
SUM(amount) FILTER (WHERE status = 'completed') AS completed_amount

-- For portability, use the equivalent CASE form
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END)

COUNT and SUM when there are 0 matching rows: COUNT(*) FILTER(...) returns 0 when no rows qualify, while SUM(...) FILTER(...) returns NULL. Safely fill the amount with COALESCE(..., 0).

Problem ▼

Using customers and orders, retrieve one row per customer with completed count (completed_count), cancelled count (cancelled_count), pending count (pending_count), and completed amount total (completed_amount). Use conditional aggregation with FILTER.

Tables used

▸ customers

customer_id	name
1	Tanaka
2	Sato
3	Yamada

▸ orders

order_id	customer_id	status	amount
101	1	completed	5000
102	1	completed	3000
103	1	cancelled	2000
104	2	completed	8000
105	2	pending	1000
106	3	cancelled	4000
107	3	pending	2000
108	3	pending	1500

Note: instead of joining three times by status, route the statuses into columns with one GROUP BY.

Expected Output ▼

name	completed_count	cancelled_count	pending_count	completed_amount
Tanaka	2	1	0	8000
Sato	1	0	1	8000
Yamada	0	1	2	0

Model Answer ▼

SELECT
  c.name,
  COUNT(*) FILTER (WHERE o.status = 'completed') AS completed_count,  -- Conditional count by status
  COUNT(*) FILTER (WHERE o.status = 'cancelled') AS cancelled_count,
  COUNT(*) FILTER (WHERE o.status = 'pending')   AS pending_count,
  COALESCE(SUM(o.amount) FILTER (WHERE o.status = 'completed'), 0) AS completed_amount  -- Completed amount total (0 when none)
FROM customers AS c
INNER JOIN orders AS o  -- Expands as 1:N
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

/*
  Execution order:
  1. FROM customers AS c     → Read customers
  2. INNER JOIN orders AS o  → Join (expands as 1:N)
  3. GROUP BY c.customer_id  → Form groups
  4. COUNT/SUM FILTER (...)  → Aggregate by status (pivot)
  5. SELECT                  → Project columns
  6. ORDER BY c.customer_id  → Sort and output
  */

Explanation (table transitions & key points) ▼

SELECT
  c.name,
  COUNT(*) FILTER (WHERE o.status = 'completed') AS completed_count,
  COUNT(*) FILTER (WHERE o.status = 'cancelled') AS cancelled_count,
  COUNT(*) FILTER (WHERE o.status = 'pending') AS pending_count,
  COALESCE(SUM(o.amount) FILTER (WHERE o.status = 'completed'), 0) AS completed_amount
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

LEGEND ▼

Rows read / loaded

① Left table — customers ▼

FROM customers AS cThese 3 customers are the aggregation axis. The final result folds the order rows into one row per customer.

1 / 6

customer_id	name
1	Tanaka
2	Sato
3	Yamada

3 customer rows

LEARNING POINTS

CONDITIONAL AGGREGATION

Conditional aggregation — fold vertical status values into horizontal columns (N→1)

Avoid repeated JOINs and aggregate multiple metrics in one scan
COUNT(*) FILTER (WHERE status = ...) ≡ SUM(CASE WHEN ...)

FILTER and CASE are equivalent — both fold N→1: COUNT(*) FILTER (WHERE status='completed') returns the same result as SUM(CASE WHEN status='completed' THEN 1 ELSE 0 END). FILTER is readable and standardized in PostgreSQL and other databases; CASE is portable across almost every database. Both implement aggregation over selected rows within a group inside one GROUP BY.

WHERE versus FILTER: an outer WHERE status='completed' removes all non-completed rows from the query, so counts for other statuses become impossible. FILTER specifies the input rows independently for each aggregate, allowing completed, cancelled, and pending orders to be counted in one grouping. FILTER means “keep the rows, but narrow each aggregate's input.”

ANTI-PATTERNS

Joining the table repeatedly for each status: repeatedly joining orders under aliases for completed, cancelled, and so on creates a Cartesian product inside each group, double-counting counts and amounts. If any required status has 0 rows, INNER JOIN also removes the customer entirely. One JOIN plus conditional aggregation is the standard pattern for multiple metrics from one table.

Ignoring NULL from SUM when there are 0 matches: SUM(amount) FILTER(...) returns NULL when no rows qualify, and using it directly allows NULL to propagate through later calculations. Fill monetary and ratio values with COALESCE(..., 0) (COUNT returns 0 and needs no fill).

FIELD NOTES

Conditional aggregation is a dashboard staple: order breakdowns by status, headcount by gender or age group in one row, and monthly sales pivoted into columns all follow this pattern. It is especially useful as a shaping query before data reaches a BI tool, and is usually far simpler than multiple JOINs or UNIONs. For a truly dynamic pivot, PostgreSQL's crosstab extension is another option.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 4

Top-N per Group with LATERAL JOIN — Controlled 1:N expansion through correlated joins

LATERALCorrelated joinTop-N per groupControlled 1:N

Background ▼

Suppose we want the 2 highest-priced products in each category. With a normal JOIN, a subquery in the FROM clause cannot reference columns from the outer table, so it cannot express a per-group restriction.

Adding LATERAL turns the subquery into a correlated join that can reference columns from each left-side row. This lets us write “run the subquery for each category, then apply ORDER BY ... LIMIT N inside it” to express Top-N per group concisely. Unlike ROW_NUMBER in Q7 of the earlier part, which returned one row per group (Top-1), this is a 1:N expansion controlled to any chosen N.

-- LATERAL: the subquery can reference columns from the left table c
FROM categories c
CROSS JOIN LATERAL (
  SELECT ... FROM products p
  WHERE p.category_id = c.category_id   -- ← Reference c from the left side (not allowed in a normal subquery)
  ORDER BY p.price DESC LIMIT 2         -- ← Limit to the top 2 in each category
) t

Choosing between CROSS and LEFT: CROSS JOIN LATERAL drops a left row when its subquery returns 0 rows (a category with no products). If empty categories should remain, use LEFT JOIN LATERAL (...) ON true.

Problem ▼

Using categories and products, retrieve the 2 highest-priced products in each category (category_name, product_name, price, rank rn). Use a correlated join with LATERAL JOIN.

Tables used

▸ categories

category_id	category_name
1	Drinks
2	Food

▸ products

product_id	category_id	product_name	price
1	1	Coffee	500
2	1	Tea	450
3	1	Juice	400
4	1	Water	200
5	2	Cake	600
6	2	Sandwich	500

Note: Drinks has 4 products, but only the top 2 (Coffee and Tea) are returned. The point is to control each group's row count to N.

Expected Output ▼

category_name	product_name	price	rn
Drinks	Coffee	500	1
Drinks	Tea	450	2
Food	Cake	600	1
Food	Sandwich	500	2

Model Answer ▼

SELECT
  c.category_name,
  t.product_name,
  t.price,
  t.rn
FROM categories AS c
CROSS JOIN LATERAL (
  SELECT
    p.product_name,
    p.price,
    ROW_NUMBER() OVER (ORDER BY p.price DESC) AS rn
  FROM products AS p
  WHERE p.category_id = c.category_id   -- Correlated: limit to products in the outer category
  ORDER BY p.price DESC
  LIMIT 2                          -- Keep the top 2 in each category (1:N → 1:2)
) AS t
ORDER BY c.category_id, t.rn;

/*
  Execution order:
  1. FROM categories AS c          → Take the 2 categories one at a time
  2. CROSS JOIN LATERAL (...)      → Run the subquery for each category c
  3. Join 2 categories with 2 each → 4 total rows (controlled 1:N)
  4. SELECT projects 4 columns
  5. ORDER BY c.category_id, t.rn  → Output by category and ascending rank
  */

Explanation (table transitions & key points) ▼

SELECT
  c.category_name,
  t.product_name,
  t.price,
  t.rn
FROM categories AS c
CROSS JOIN LATERAL (
  SELECT
    p.product_name,
    p.price,
    ROW_NUMBER() OVER (ORDER BY p.price DESC) AS rn
  FROM products AS p
  WHERE p.category_id = c.category_id
  ORDER BY p.price DESC
  LIMIT 2
) AS t
ORDER BY c.category_id, t.rn;

LEGEND ▼

Rows read / loaded

① Left table — categories ▼

FROM categories AS cThese 2 categories are the axis for Top-N. LATERAL runs the subquery once for each of these rows.

1 / 6

category_id	category_name
1	Drinks
2	Food

2 category rows

LEARNING POINTS

LATERAL / TOP-N PER GROUP

Correlated join — run a subquery for each left row and take Top-N

Control 1:N expansion to N rows per group
categories ⋈ LATERAL(products ... LIMIT 2) → controlled to 1:2

Why LATERAL is needed: a normal subquery in the FROM clause is evaluated independently and cannot reference columns from the outer table. It therefore cannot express a subquery whose contents change with the left row (Top-N per group). LATERAL runs the subquery in sequence for each left row, allowing correlation such as WHERE p.category_id = c.category_id and LIMIT N in the same subquery.

Comparison with the ROW_NUMBER approach: as in Q7 of the earlier part, calculate ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) and filter with WHERE rn <= 2 to get the same result. The difference is that ROW_NUMBER numbers every product before filtering, whereas LATERAL can stop after taking 2 products per category. With many target groups and a suitable index, LATERAL is often more efficient.

ANTI-PATTERNS

Forgetting the correlation condition and returning every product: if you omit WHERE p.category_id = c.category_id, every category receives the Top2 products from all categories, producing meaningless results. Correlation is the essence of LATERAL, so always include a WHERE clause that references a left-side column.

Missing that categories with zero products disappear: CROSS JOIN LATERAL removes a category when its subquery returns 0 rows. If the requirement is to show empty categories too, use LEFT JOIN LATERAL (...) ON true and retain the row where product-side values are NULL (the same idea as the earlier LEFT JOIN question).

FIELD NOTES

Top-N per group appears constantly in practice: each user's 3 most recent orders, each store's 5 highest-selling products, or the 3 newest comments on each article. MySQL 8.0+ and PostgreSQL support LATERAL; SQL Server uses CROSS APPLY, and some databases use the ROW_NUMBER approach instead. When you see “top N rows per group,” train yourself to think of LATERAL or ROW_NUMBER <= N.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 5

Expand Hierarchies with a Recursive CTE — Traverse a self-referencing 1:N organization chart one level at a time

Recursive CTEWITH RECURSIVESelf-referencing 1:NHierarchy expansion

Background ▼

An organization chart where each employee points to a manager with manager_id is a self-referencing 1:N relationship. Because the number of levels is unknown, a fixed number of JOINs cannot traverse the entire hierarchy.

A recursive CTE (WITH RECURSIVE) ① creates an anchor, ② joins that result in a recursive term to generate the next level, and ③ repeats until no new rows appear. The key is that at every step, only the rows added in the current step become the next input (driving table).

WITH RECURSIVE org AS (
  <anchor>                    -- Starting point (for example, the top-level president, level=1)
  UNION ALL
  <recursive term ... JOIN org> -- Join the previous result (org) to generate the next level
)                             -- Stop when the recursive term returns 0 rows

New rows drive the next step: the org referenced by the recursive term is not the full accumulated result; it is the rows newly generated in the immediately preceding step. The process goes president → direct reports → their reports, moving one level deeper from the latest additions.

Problem ▼

From employees, a self-referencing table where manager_id points to a manager, retrieve each employee's hierarchy level (level: top level = 1) and path (path: titles from the president joined with >). Use a recursive CTE.

Tables used

▸ employees

emp_id	name	manager_id
1	President	NULL
2	Manager A	1
3	Manager B	1
4	Section Chief A	2
5	Staff A	4

Note: hierarchy depth is unknown. Use WITH RECURSIVE to expand every level one step at a time instead of using a fixed number of JOINs.

Expected Output ▼

emp_id	name	level	path
1	President	1	President
2	Manager A	2	President > Manager A
3	Manager B	2	President > Manager B
4	Section Chief A	3	President > Manager A > Section Chief A
5	Staff A	4	President > Manager A > Section Chief A > Staff A

Model Answer ▼

WITH RECURSIVE org AS (
  -- Anchor: start from the top-level employee with no manager (President)
  SELECT
    emp_id, name, manager_id,
    1 AS level,
    name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  -- Recursive term: add direct reports of the previous org result one level at a time
  SELECT
    e.emp_id, e.name, e.manager_id,
    o.level + 1,
    o.path || ' > ' || e.name
  FROM employees AS e
  INNER JOIN org AS o ON e.manager_id = o.emp_id
)
SELECT emp_id, name, level, path
FROM org
ORDER BY level, emp_id;

/*
  Execution order (recursive flow):
  1. Run the anchor             → Generate the starting row (President)
  2. Recursive term, iteration 1 → Expand children
  3. Recursive term, iteration 2 → Expand children
  4. Recursive term, iteration 3 → Expand children
  5. Recursive term, iteration 4 → Stop with no new rows
  6. SELECT ... ORDER BY        → Sort and output
  */

Explanation (table transitions & key points) ▼

WITH RECURSIVE org AS (
  SELECT emp_id, name, manager_id,
    1 AS level, name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.name, e.manager_id,
    o.level + 1, o.path || ' > ' || e.name
  FROM employees AS e
  INNER JOIN org AS o ON e.manager_id = o.emp_id
)
SELECT emp_id, name, level, path
FROM org
ORDER BY level, emp_id;

LEGEND ▼

Rows read / loaded

① Source data — employees (self-reference) ▼

FROM employeesmanager_id points to emp_id in the same table, creating a self-referencing 1:N structure. The President has no manager, so manager_id = NULL. We expand the hierarchy one level at a time from here.

1 / 7

emp_id	name	manager_id
1	President	NULL
2	Manager A	1
3	Manager B	1
4	Section Chief A	2
5	Staff A	4

5 employee rows (manager_id → emp_id self-reference)

LEARNING POINTS

RECURSIVE CTE

WITH RECURSIVE — expand the hierarchy one level at a time from the anchor

Newly added rows become the next driving table and move deeper
Anchor → recursive term (JOIN org) → stop at 0 rows

Three elements of a recursive CTE: ① the anchor (evaluate the starting point once), ② UNION ALL, and ③ the recursive term (reference the CTE itself, org, to generate the next rows). The recursive term takes only the rows added immediately before as input, joins them to employees, and creates the next level. It stops automatically when no new rows are produced (0 rows returned).

Distinguish the accumulated result from the current additions: the final org contains the accumulated hierarchy (5 rows), but each recursive step is driven by only the rows generated in that iteration. Think of the baton passing from President (1 row) → managers (2 rows) → chief (1 row) → staff (1 row) → 0 rows and accumulating along the way; this makes recursive CTE behavior much easier to read.

ANTI-PATTERNS

Changing UNION ALL to UNION: UNION removes duplicates (and typically sorts) at every iteration, adding unnecessary work. Hierarchies normally do not produce duplicates, so UNION ALL is the rule. However, if the data can contain a cycle such as A→B→A, track visited nodes to prevent an infinite loop.

Creating an infinite loop by omitting a termination condition: the recursive term must be structured so that it eventually returns 0 rows. Many databases impose a recursion-depth limit, but during design verify that following parents eventually stops. If cycles are possible, add protection such as checking whether the current employee already appears in path.

FIELD NOTES

Recursive CTEs are a general-purpose tool for hierarchies and graph-shaped data: organizational reporting lines, bills of materials (BOM: product → component → material), all descendants in a category tree, comment-thread expansion, and generated number or date sequences. Use WHERE manager_id = a specific ID as the anchor to retrieve only everyone under one employee. When you see a self-referencing table, remember recursive CTEs.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.