Common Table Expressions (CTE)

Explanation

A Common Table Expression (CTE) is a named subquery introduced by the WITH clause. Think of it as a scratchpad: you define a query, give it a name, and then use that name in the main query as if it were a table.

CTEs shine when a subquery is used more than once, or when nesting gets so deep that the logic is hard to follow. They make SQL read top-to-bottom instead of inside-out.

Syntax

WITH name AS (
  SELECT col FROM table
)
SELECT * FROM name;

Interactive Example

Define a CTE of department averages, then list employees who beat their department average. Then chain two CTEs to compute company-wide stats.

Common Mistakes

• Forgetting the comma between multiple CTEs. The pattern is WITH a AS (...), b AS (...) — the WITH appears only once.
• Referencing a CTE before it's defined. A CTE can only see earlier CTEs in the same WITH list (unless it's recursive).
• Thinking a CTE is reusable across statements. It lives only for the single query that defines it.

Best Practices

• Replace deeply nested subqueries with CTEs to improve readability.
• Name CTEs by what they represent (dept_avg, high_earners).
• Chain CTEs to build a pipeline: each step transforms the previous one.

Practice Question

Write a CTE that computes total salary per department, then in the main query return only departments whose total salary exceeds the average of those totals.

Summary

A CTE (WITH name AS (...)) names a subquery so you can treat it like a table within one statement. Chain multiple CTEs for readable, step-by-step transforms, and prefer them over nested subqueries for clarity.

FAQ