CTEs (WITH clauses) replaced subqueries as the modern way to structure complex queries. Readable, debuggable, recursive. Snowflake, BigQuery, and Postgres all optimize them aggressively. By the end of this lesson you'll write multi-step analytical queries the way senior engineers do.
Learning Objectives
After this lesson, you will be able to:
Write subqueries in WHERE, FROM, and SELECT clauses
Explain the difference between correlated and uncorrelated subqueries
Use Common Table Expressions (WITH) to make complex queries readable
Chain multiple CTEs together in a single query
Write recursive CTEs for hierarchical data like org charts and category trees
Decide when to use a subquery vs. a CTE vs. a JOIN
Recognize the Postgres 12+ CTE inlining change and why it matters for performance
If subqueries feel intimidating at first, don't worry -- the core idea is simple. You already know how to write a SELECT query. A subquery is just putting one SELECT inside another. Think of it like calling a function inside another function in any programming language.
Your Reflection
Saves automatically
What’s one thing you learned? What’s still confusing?
-- Find all employees who earn more than the average salary
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
-- The inner query returns ONE number (e.g., 65000)
-- The outer query becomes: WHERE salary > 65000
-- Find employees in departments that are located in New York
SELECT name, department_id
FROM employees
WHERE department_id IN (
SELECT id
FROM departments
WHERE city = 'New York'
);
-- The inner query returns a list: (1, 4, 7)
-- The outer query becomes: WHERE department_id IN (1, 4, 7)
-- Find customers who have placed at least one order
SELECT c.name, c.email
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);
-- EXISTS just checks: "does at least one row exist?"
-- It does NOT care what columns you select (SELECT 1 is convention)
What Do You Think?
What does this query return? SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
Try it! Write a query that finds all products priced higher than the average price. Then modify it to find products priced higher than the average price within their own category (hint: you will need a correlated subquery).
A correlated subquery references the outer query. It runs once for every row in the outer query, unlike a regular subquery which runs just once.
sql
-- Find employees who earn more than the average salary IN THEIR department
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id -- references outer query!
);
The key difference:
Uncorrelated subquery: inner query is independent, runs once
Correlated subquery: inner query depends on the outer row, runs once per row
sql
-- Another example: find the most recent order for each customer
SELECT o.customer_id, o.order_date, o.total
FROM orders o
WHERE o.order_date = (
SELECT MAX(o2.order_date)
FROM orders o2
WHERE o2.customer_id = o.customer_id -- correlated!
);
You can use a subquery as a temporary table in the FROM clause:
sql
-- Calculate each department's average salary, then find the highest
SELECT dept_name, avg_salary
FROM (
SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.name
) AS dept_averages
ORDER BY avg_salary DESC
LIMIT 1;
The subquery creates a temporary result set (a "derived table") that the outer query treats like a regular table.
You can compute a value for each row using a subquery in the SELECT clause:
sql
-- Show each employee alongside their department's average salary
SELECT
e.name,
e.salary,
(SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id) AS dept_avg_salary,
e.salary - (SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id) AS above_avg_by
FROM employees e
ORDER BY above_avg_by DESC;
This works but is ugly and repetitive. CTEs solve this.
-- The same department average query, but readable
WITH dept_averages AS (
SELECT
d.name AS dept_name,
AVG(e.salary) AS avg_salary,
COUNT(*) AS employee_count
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.name
)
SELECT dept_name, avg_salary, employee_count
FROM dept_averages
WHERE avg_salary > 70000
ORDER BY avg_salary DESC;
This is where CTEs truly shine. You can define multiple named steps:
Trace how data flows through each chained CTE step interactively:
Loading visualization...
sql
WITH
-- Step 1: Calculate department averages
dept_stats AS (
SELECT
department_id,
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary,
COUNT(*) AS headcount
FROM employees
GROUP BY department_id
),
-- Step 2: Find high-performing departments
top_departments AS (
SELECT department_id
FROM dept_stats
WHERE avg_salary > 70000
AND headcount >= 5
),
-- Step 3: Get employees in those departments
top_dept_employees AS (
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.department_id IN (SELECT department_id FROM top_departments)
)
-- Final: Show results with department stats
SELECT
tde.name,
tde.salary,
ds.avg_salary AS dept_avg,
tde.salary - ds.avg_salary AS above_avg_by
FROM top_dept_employees tde
JOIN dept_stats ds ON tde.department_id = ds.department_id
ORDER BY above_avg_by DESC;
Each CTE is a building block. You can read the query top-to-bottom and understand each step. Compare this to the nested subquery version -- night and day.
What Do You Think?
How many times can you reference a CTE (defined with WITH) in the main query?
Try it! Rewrite a nested subquery as a CTE. Start with the query below and break it into named steps using WITH.
Recursive CTEs let SQL handle hierarchical or graph-like data -- things like org charts, category trees, or threaded comments.
sql
-- Employee org chart: find all reports under a manager (at any level)
WITH RECURSIVE org_chart AS (
-- Base case: start with the CEO (manager_id is NULL)
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive case: find employees who report to someone already in the result
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
REPEAT(' ', level - 1) || name AS org_tree,
level
FROM org_chart
ORDER BY level, name;
How recursive CTEs work:
Base case runs first (the part before UNION ALL)
Recursive case joins new rows against the previous iteration's results
Repeats until the recursive case produces no new rows
Final result is the UNION of all iterations
sql
-- Generate a sequence of numbers (useful for date ranges, etc.)
WITH RECURSIVE numbers AS (
SELECT 1 AS n -- base case
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10 -- recursive case
)
SELECT n FROM numbers;
-- Returns: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
Rule of thumb: If a subquery is used once and is short, leave it inline. If it is used multiple times or the query has more than two levels of nesting, refactor to CTEs. If you are traversing a hierarchy, use a recursive CTE.
If you made it through recursive CTEs, you are handling genuinely advanced SQL concepts. These patterns take time to feel natural -- practice writing a few CTEs on your own data and the syntax will click. You are building skills that separate SQL beginners from SQL practitioners.
What's next: Window functions. CTEs let you compose multi-step queries; window functions let you compute "rank within group" and "running total" without collapsing rows. Window functions are the single biggest leap between intermediate and senior SQL — every analytics query at Stripe, Airbnb, and Netflix uses them.
Quick Check1 / 5
What is the difference between a correlated and an uncorrelated subquery?