SQL CTEs (WITH Clause)

The previous module's top-N-per-group query buried its ranking logic inside a subquery, nested in a FROM clause, read from the inside out. A CTE names that same logic and puts it first, read top to bottom instead, exactly the cleaner rewrite that lesson promised.

Learning Objectives

• Define a CTE precisely: a named, temporary result set, usable only within the single query that defines it.
• Rewrite a subquery as a CTE, and explain when that's actually worth doing.
• Distinguish a CTE from both a subquery and a VIEW (Module 10).

What a CTE Actually Is

A CTE (Common Table Expression) is a named, temporary result set, defined with WITH before the main query, and referenced by that name inside it. It exists only for the duration of that one query, there's nothing left behind afterward, unlike a VIEW, which is saved under its name permanently until explicitly dropped.

Rewriting the Top-N-Per-Group Subquery as a CTE

Identical result to the subquery version from the previous module, read in the natural order a query actually executes in: define ranked first, then query it, instead of nesting the ranking logic inside a FROM clause and reading inside-out.

WITH ranked AS (
  SELECT first_name, last_name, department_id, salary,
         ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
  FROM employees
)
SELECT * FROM ranked
WHERE salary_rank <= 2;

CTE vs Subquery vs VIEW

Try It Yourself

Rewrite this subquery as a CTE: SELECT * FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) dept_avg WHERE avg_salary > 70000;, on PostgreSQL.

WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
)
SELECT * FROM dept_avg
WHERE avg_salary > 70000;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Reach for a CTE over a subquery whenever naming the intermediate result would make the query noticeably easier to follow.
• Don't assume a CTE is a performance optimization by itself, verify with the actual execution plan if performance specifically matters.
• Use a VIEW instead of a CTE when the same named result genuinely needs to be queried across many separate statements, not just once.

Interview Questions

Summary

A CTE is a named, temporary result set, defined with WITH and read before the query that uses it, functionally similar to a subquery but far more readable once logic gets nested more than one level deep. It never persists beyond its one query, that's what a VIEW is for instead. PostgreSQL before version 12 is the one database here where a CTE and an equivalent subquery could genuinely perform differently.

What's Next?

Multiple CTEs is next: defining more than one named result in a single WITH clause, with later ones able to build on earlier ones.