cte

CTE vs Subquery: Same Result, Clearer Code

Rewrite a nested subquery as a CTE to compute above-average-salary employees.

Overview

Any query you can write with a derived table (subquery in FROM) you can also write with a CTE — and the CTE version is usually far easier to read. This example finds employees earning above the company average, first as a subquery, then as an equivalent CTE.

The Query

Employees & Departments

Loading database engine...

Step-by-Step Breakdown

avg_salary CTE — computes the single company-wide average once.
FROM employees e, avg_salary a — a cross join to that one-row CTE so every employee row can compare against company_avg.
WHERE e.salary > a.company_avg — keeps only above-average earners.

When to prefer a CTE

If a subquery is referenced multiple times, or nested more than one level deep, a CTE dramatically improves readability.

Variations

The equivalent subquery version:

Employees & Departments

Loading database engine...

Common Mistakes

Thinking CTEs are faster. They're typically optimized the same as subqueries; choose based on clarity.
Cross join without a guard. FROM employees, avg_salary is fine here because avg_salary is exactly one row; with multiple rows it becomes a cartesian product.
Overusing single-use CTEs. If a CTE is used once and simple, a subquery in FROM may be just as clear.