Subqueries and CTEs Updated November 2026

WITH (CTE)

WITH name AS (SELECT ...) defines a common table expression (CTE), a named result you can query like a table in the statement that follows. CTEs make multi-step queries readable: compute totals per customer in one CTE, then filter or join them in the main query. Separate several CTEs with commas; WITH RECURSIVE walks hierarchies.

Syntax

WITH cte_name AS (SELECT ...), other_cte AS (SELECT ...) SELECT ... FROM cte_name;

Examples

WITH totals AS (
  SELECT customer_id, SUM(amount) AS spent
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, t.spent
FROM totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.spent > 1000;

Result: Big spenders with their total

WITH ranked AS (
  SELECT name, dept, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, dept, salary FROM ranked WHERE rn = 1;

Result: The top earner in each department

Window functions cannot be used in WHERE directly, so a CTE (or subquery) is the standard wrapper.

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.level + 1
  FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT name, level FROM chain ORDER BY level, name;

Result: Every employee with their depth in the org chart

The first SELECT is the anchor, the second references the CTE itself. Works in SQLite and PostgreSQL.

Common mistakes

Putting a semicolon or a blank statement between the CTE and the main query; the WITH block and the SELECT are one statement.
Writing WITH a AS (...) WITH b AS (...): the second CTE is introduced with a comma, not another WITH.
A recursive CTE without a termination condition loops forever; make sure the recursive part eventually returns no rows.

Practice WITH (CTE)

These drills check your answer on live data, free, no account needed.

Big spendersCalculate each customer's total paid spend, then return the names of customers whose total is above the average of those totals. Only customers with at least one paid order count in the average.
Top earner per departmentReturn the dept_id, name and salary of the highest-paid employee in each department. Ignore employees without a department.

Questions

CTE vs subquery: which should I use?

They do the same work. A CTE is more readable when the inner query is long or used more than once; a subquery is fine for a short one-off. Performance is usually the same.

Can I use a CTE in UPDATE or DELETE?

Yes. Both SQLite and PostgreSQL allow WITH before UPDATE, DELETE and INSERT, for example to pick the ids to delete in a CTE.

SubqueryA subquery is a SELECT inside another query. Scalar subqueries (compare to an average), IN subqueries, correlated subqueries and derived tables in FROM.
Window functionsWindow functions compute rankings, running totals, shares and previous-row values without collapsing rows. The OVER clause, PARTITION BY, ORDER BY and frames.
ROW_NUMBERROW_NUMBER() OVER (ORDER BY ...) assigns 1, 2, 3 to rows; with PARTITION BY it restarts per group. Latest row per customer, remove duplicates, paginate.
Self joinA self join joins a table to itself using two aliases. Typical uses: employee and manager in the same table, comparing rows in pairs, rows that share a value.