Subqueries & CTEs

A subquery is a SELECT nested inside another statement. A scalar subquery returns one value (SELECT AVG(salary) FROM employees). A list subquery feeds IN, EXISTS, ANY or ALL. A derived table is a subquery in FROM that acts like a temporary table for that query.

A correlated subquery refers to a column of the outer query, so it is logically evaluated once per outer row: 'employees whose salary is above the average of their own department'. The planner often rewrites it into a join, but the meaning is per row.

A common table expression (CTE) is a named subquery written up front: WITH dept_avg AS (...) SELECT ... FROM dept_avg. It makes multi-step logic readable and can be referenced more than once. WITH RECURSIVE lets a CTE refer to itself, which is how SQL walks trees and graphs such as org charts or category hierarchies.

1. 1A scalar subquery must return at most one row and one column; if it returns no rows the value is NULL, and if it returns two rows the query fails at run time.
2. 2IN (subquery) is TRUE if the value equals any returned value. EXISTS (subquery) is TRUE if the subquery returns at least one row; what it selects does not matter, so SELECT 1 is the convention.
3. 3A correlated subquery like WHERE salary > (SELECT AVG(salary) FROM employees i WHERE i.dept = e.dept) recomputes the inner query for each outer row's dept. Optimisers frequently turn this into a join against pre-aggregated results.
4. 4A CTE is defined with WITH name AS (query) and then used like a table. Several CTEs can be chained, each one reading earlier ones. In PostgreSQL 12 and later, a non-recursive CTE used once is usually inlined into the main query (you can force or prevent this with MATERIALIZED / NOT MATERIALIZED); before version 12 every CTE was computed separately, which could block optimisations. MySQL supports CTEs from 8.0.
5. 5A recursive CTE has two parts joined by UNION ALL (or UNION): an anchor query that produces the starting rows, and a recursive query that joins the CTE's previous output to the base table to produce the next level. It repeats until a step produces no new rows.

-- employees(id, name, dept, salary, manager_id)

-- 1) Employees earning above their department average
WITH dept_avg AS (
  SELECT dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept
)
SELECT e.name, e.dept, e.salary
FROM employees e
JOIN dept_avg d ON d.dept = e.dept
WHERE e.salary > d.avg_salary;

-- 2) Everyone in Asha's reporting chain, with depth
WITH RECURSIVE chain AS (
  SELECT id, name, 0 AS level
  FROM employees
  WHERE id = 1                          -- anchor: Asha
  UNION ALL
  SELECT e.id, e.name, c.level + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id   -- next level down
)
SELECT name, level FROM chain ORDER BY level, name;

Query 1 returns Asha, Farah and Eli on the example table. Query 2 returns Asha 0, then Ben, Chen and Farah at level 1, then Eli at level 2. The RECURSIVE keyword is required in PostgreSQL and MySQL 8.0.

Cost: A naively executed correlated subquery is O(n x m); planners often decorrelate it into a join, about O(n + m) with hashing. A recursive CTE costs roughly the sum of each level's join, proportional to the number of rows reached when the join column is indexed.