SQL CTE vs Subquery: When to Use Each

They often solve the same problem

A subquery nests one query inside another. A common table expression gives an intermediate result a name with WITH. In many databases the optimizer can produce the same execution plan for both forms.

Use a subquery for a compact single step

A subquery works well when the intermediate logic is short and used only once.

SELECT employee_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

Use a CTE to explain a multi-step transformation

A CTE is often easier to review when you need to name a business step, reuse its output, or debug the query one stage at a time.

WITH department_pay AS (
  SELECT
    department_id,
    AVG(salary) AS average_salary
  FROM employees
  GROUP BY department_id
)
SELECT d.department_name, p.average_salary
FROM department_pay p
JOIN departments d ON d.department_id = p.department_id;

Choose for clarity, then verify performance

Do not assume a CTE is automatically faster or slower. Start with the clearest correct query, inspect the execution plan on real data, and optimize only when measurement shows a problem.

• Name CTEs after the business meaning
• Keep each step focused
• Avoid unnecessary nesting
• Check whether your database materializes a CTE