SQL Subqueries vs CTEs: Types, Differences, Performance, and When to Use Each

SQL Subqueries vs CTEs: Types, Differences, Performance, and When to Use Each

Subqueries and Common Table Expressions (CTEs) are two of the most common ways to break complex SQL logic into smaller, reusable pieces. While they often produce the same results, they differ in readability, reusability, and—in some cases—performance. learnsql

What Are Subqueries?

A subquery is a query nested inside another query. It can appear in the SELECT, FROM, WHERE, or HAVING clauses. learnsql

Types of Subqueries

• Non-correlated subqueries: Run independently once, then return results to the outer query. dev
• Correlated subqueries: Reference columns from the outer query and run once per row—often slower on large datasets. dev

Example:

-- Non-correlated: find employees earning more than average salary
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated: find employees earning more than their department average
SELECT e.name, e.salary, e.department
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) FROM employees WHERE department = e.department
);

What Are CTEs?

A Common Table Expression (CTE) is a named temporary result set defined using the WITH clause. It exists only for the duration of the query and can be referenced multiple times. learnsql

Example:

WITH avg_salary AS (
    SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT name, salary
FROM employees, avg_salary
WHERE salary > avg_sal;

Key Differences Between Subqueries and CTEs

Feature	Subqueries	CTEs
Definition location	Inline, inside the main query	Defined at the top with WITH
Naming	Not required (except in PostgreSQL)	Must be named
Reusability	Can be used only once per occurrence	Can be referenced multiple times in the same query
Readability	Can become hard to read when deeply nested	Breaks logic into named, readable steps
Use in WHERE with IN/EXISTS	Yes	No (CTEs must be joined or referenced differently)
Recursive queries	Not supported	Supported via recursive CTEs
Performance (modern DBs)	Often identical to CTEs	Often identical to subqueries; older PostgreSQL materialized CTEs

Performance: Subqueries vs CTEs

The Short Answer

In modern databases (PostgreSQL 12+, MySQL 8+, SQL Server 2019+, Snowflake, BigQuery), there is typically no performance difference between equivalent subqueries and CTEs. The query optimizer treats them similarly and often generates identical execution plans. dev

When Performance Can Differ

• Older PostgreSQL versions (<12): CTEs were always materialized (computed once and stored), which could make them slower than inlined subqueries. dev
• Repeated logic: If the same subquery appears multiple times, the database may recompute it each time, whereas a CTE is computed once and reused. dev
• Correlated subqueries: These can be slow on large tables because they run once per row, but modern optimizers sometimes rewrite them as joins. dev

What Actually Affects Performance

Performance depends more on:

• Indexing and join conditions
• Data size and distribution
• Execution plan chosen by the optimizer
• Partitioning and statistics

—not just whether you use a subquery or CTE. linkedin

When to Use Subqueries

Use a subquery when:

• You need a single value or quick filter (e.g., WHERE salary > (SELECT AVG(salary) ...)). learnsql
• The logic is short, simple, and used only once. datawithsarah
• You’re using IN or EXISTS in a WHERE clause. learnsql
• You’re writing a quick ad-hoc query where readability isn’t critical. dev

When to Use CTEs

Use a CTE when:

• Your query is long or has multiple logical steps. learnsql
• You need to reference the same intermediate result multiple times. learnsql
• You want to improve readability by naming each step. datawithsarah
• You’re writing recursive queries (e.g., hierarchical data like org charts). learnsql
• You’re building production-level queries or ETL pipelines where maintainability matters. datawithsarah

Practical Example: Subquery vs CTE

Subquery version:

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
)
AND department IN (
    SELECT department FROM departments WHERE location = 'New York'
);

CTE version (more readable):

WITH avg_salary AS (
    SELECT AVG(salary) AS avg_sal FROM employees
),
ny_departments AS (
    SELECT department FROM departments WHERE location = 'New York'
)
SELECT e.name, e.salary
FROM employees e
JOIN ny_departments nd ON e.department = nd.department
CROSS JOIN avg_salary
WHERE e.salary > avg_sal;

Bottom Line

• Subqueries are great for simple, one-off logic and inline filters. datawithsarah
• CTEs shine when queries get complex, need multiple steps, or require reuse. learnsql
• Performance is usually similar in modern databases—focus on readability and maintainability. dev

Choose the tool that makes your SQL clearer and easier to maintain, and always check the execution plan if performance is a concern.