SQL Common Table Expressions (CTEs) | Learn Computer Academy

Common Table Expressions (CTEs)

A CTE (Common Table Expression) names a temporary result set with WITH, making complex queries easier to read and reuse within the same statement.

WITH ... AS

Defines a named, temporary result set that the main query can reference like a table.

WITH cte_name AS (
  SELECT ...
)
SELECT * FROM cte_name;

WITH DeptAvg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM Employee
  GROUP BY department
)
SELECT * FROM DeptAvg
WHERE avg_salary > 60000;

The CTE is computed once and can be referenced by name afterward.
Reads top-to-bottom, unlike a nested subquery — this is the readability win over the "subquery in FROM" pattern from the last lesson.

Common Mistake

Reaching for a CTE purely for style when a simple subquery would do just as well.

Recursive CTE

A CTE that refers to itself, used to walk hierarchical or graph-like data.

WITH RECURSIVE cte_name AS (
  base_case
  UNION ALL
  recursive_case
)
SELECT * FROM cte_name;

WITH RECURSIVE OrgChart AS (
  SELECT id, name, manager_id FROM Employee WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM Employee e
  JOIN OrgChart o ON e.manager_id = o.id
)
SELECT * FROM OrgChart;

The base case picks the starting rows (the top of the hierarchy).
The recursive case repeatedly joins back until no new rows are found.

Common Mistake

Writing a recursive case with no terminating condition, causing an infinite loop.

CTEs are the last of the "advancing" querying techniques in this section. The final lesson steps past everyday querying entirely, into tools that sit past the beginner track: views, indexes, stored procedures, triggers, and how to check whether a query is actually fast.