WITH: named queries

168 min

Complex queries quickly become a tangle: a subquery inside a subquery… WITH (a CTE — Common Table Expression) splits them into clear, named steps.

The pattern

WITH name AS (
  SELECT …
)
SELECT … FROM name;

name is a temporary table that exists only for this one query.

Example: students with a high average

First we work out each student’s average score, then we use it:

university· Try changing it

WITH avg_scores AS (
  SELECT student_id, AVG(score) AS avg_score
  FROM grades
  GROUP BY student_id
)
SELECT s.first_name, s.last_name, ROUND(a.avg_score, 1) AS avg_score
FROM avg_scores a
JOIN students s ON s.id = a.student_id
WHERE a.avg_score >= 85
ORDER BY a.avg_score DESC;

You could write the same with a subquery, but WITH is much easier to read: every step has a name.

Several CTEs

They are separated by commas, and each one can use the ones before it:

shop· Try changing it

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
),
big_orders AS (
  SELECT order_id FROM order_totals WHERE total > 10000000
)
SELECT o.payment_method, COUNT(*) AS big_orders
FROM big_orders b
JOIN orders o ON o.id = b.order_id
GROUP BY o.payment_method;

Recursive CTEs

WITH RECURSIVE calls itself — for data shaped like a tree (manager → report → their report…):

company· Try changing it

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

The first part is the starting point (the CEO); the second goes one level down each time.

1Exercise

Use WITH to count each driver’s completed trips, and show driver_id and the trip count for drivers with more than 40.

Remember

WITH name AS (…) splits a query into named steps.
Several CTEs are separated by commas; a later one can use an earlier one.
WITH RECURSIVE handles trees and chains.

WITH: named queries

168 min

Complex queries quickly become a tangle: a subquery inside a subquery… WITH (a CTE — Common Table Expression) splits them into clear, named steps.

The pattern

WITH name AS (
  SELECT …
)
SELECT … FROM name;

name is a temporary table that exists only for this one query.

Example: students with a high average

First we work out each student’s average score, then we use it:

university· Try changing it

WITH avg_scores AS (
  SELECT student_id, AVG(score) AS avg_score
  FROM grades
  GROUP BY student_id
)
SELECT s.first_name, s.last_name, ROUND(a.avg_score, 1) AS avg_score
FROM avg_scores a
JOIN students s ON s.id = a.student_id
WHERE a.avg_score >= 85
ORDER BY a.avg_score DESC;

You could write the same with a subquery, but WITH is much easier to read: every step has a name.

Several CTEs

They are separated by commas, and each one can use the ones before it:

shop· Try changing it

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
),
big_orders AS (
  SELECT order_id FROM order_totals WHERE total > 10000000
)
SELECT o.payment_method, COUNT(*) AS big_orders
FROM big_orders b
JOIN orders o ON o.id = b.order_id
GROUP BY o.payment_method;

Recursive CTEs

WITH RECURSIVE calls itself — for data shaped like a tree (manager → report → their report…):

company· Try changing it

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

The first part is the starting point (the CEO); the second goes one level down each time.

1Exercise

Use WITH to count each driver’s completed trips, and show driver_id and the trip count for drivers with more than 40.

Remember

WITH name AS (…) splits a query into named steps.
Several CTEs are separated by commas; a later one can use an earlier one.
WITH RECURSIVE handles trees and chains.

SQLab

WITH: named queries

The pattern

Example: students with a high average

Several CTEs

Recursive CTEs

Remember

WITH: named queries

The pattern

Example: students with a high average

Several CTEs

Recursive CTEs

Remember

SQLab