Latticework — Master the skills engineers actually use

Explanation

A CTE (Common Table Expression) — the WITH clause — names a subquery's result so you can reference it like a table for the rest of the query. It's the same computation a subquery does, written so it reads top-to-bottom instead of inside-out.

WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT * FROM dept_avg ORDER BY avg_salary DESC;

Try it

Once defined, a CTE behaves like any other table for the rest of the statement — you can filter it, group it, or join it to other tables.

Loading editor…

Explanation

The real advantage over a subquery shows up once you need the SAME intermediate result more than once, or want to JOIN back against the original table:

WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT e.name
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;

Written as a nested subquery instead, this same logic would need the AVG(salary) computation repeated or awkwardly duplicated — the CTE lets you compute it once, name it, and join against it directly.

Exercise

The `employees` table has columns `id, name, department, salary`. Using a CTE named `dept_avg`, write a query that returns each `department` and its average salary as `avg_salary`, ordered by avg_salary descending.

Exercise

Using a CTE, write a query that returns the `name` of every employee who earns MORE than their own department's average salary, ordered by name.

Quiz

What's the main readability benefit of a CTE (WITH clause) over an equivalent nested subquery?

Checkpoint

You can define a CTE with WITH and reference it like a table, including joining it back against the original data.

CTEs