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

Loading playground environment...

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:

Loading playground environment...

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.

Related examples

Common Table Expressions (CTEs) With WITH

cte

Use a WITH clause to compute total spend per customer as a named, reusable result set, then filter and join against it.

Derived Table: Subquery in the FROM Clause

subquery

Wrap an aggregated subquery in FROM to rank products by total revenue.

Multiple CTEs in One Query

cte

Chain several CTEs to compute department stats then rank departments by average salary.

Cite this resource

SQLSimplified. "CTE vs Subquery: Same Result, Clearer Code Example". Available at: https://sqlsimplified.online/examples/cte-vs-subquery