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

When working with SQL in analytics, reporting, or data engineering workflows, two powerful tools help structure complex logic:

• Subqueries
• Common Table Expressions (CTEs)

Understanding when to use each improves query readability, performance, and maintainability—especially when building dashboards or transforming datasets for analysis.

This article explains:

• what subqueries are
• types of subqueries
• when to use subqueries
• what CTEs are
• types of CTEs
• performance comparison
• when to choose subqueries vs CTEs

I started paying closer attention to the difference between subqueries and CTEs while working on analytics queries that became harder to debug as they grew more complex.

What Is a Subquery?

A subquery is a query nested inside another SQL query.

It executes first, and its output becomes input for the outer query.

Example:

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

Here:

• the inner query calculates the average salary
• the outer query filters employees earning above average

Subqueries are useful for embedding logic directly inside SQL statements.

---

Types of Subqueries

Subqueries can return different result structures depending on how they are used.

1. Scalar Subquery

Returns one value

Example:

SELECT name,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

Use case:

Comparisons or calculated columns

---

2. Single-Row Subquery

Returns exactly one row

Example:

SELECT *
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

Common operators:

=  >  <  >=  <=

---

3. Multi-Row Subquery

Returns multiple rows

Example:

SELECT *
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Nairobi'
);

Common operators:

IN
ANY
ALL

---

4. Correlated Subquery

Runs once for every row in the outer query

Example:

SELECT e.name, e.salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

Use carefully with large datasets because they may affect performance.

---

When Should You Use Subqueries?

Subqueries are best when:

• filtering using aggregated values
• comparing values dynamically
• embedding quick logic inside WHERE clauses
• simplifying small calculations
• avoiding temporary tables

They work especially well when logic is used once only.

---

What Is a CTE (Common Table Expression)?

A Common Table Expression (CTE) is a temporary named result set created using the WITH clause.

It improves readability and breaks complex queries into logical steps.

Example:

WITH avg_salary AS (
    SELECT AVG(salary) AS avg_sal
    FROM employees
)

SELECT *
FROM employees
WHERE salary > (SELECT avg_sal FROM avg_salary);

Think of a CTE as a temporary table that exists during query execution.

---

Types of CTEs

1. Non-Recursive CTE

Used for simplifying complex logic

Example:

WITH department_totals AS (
    SELECT department_id,
           SUM(salary) AS total_salary
    FROM employees
    GROUP BY department_id
)

SELECT *
FROM department_totals;

Best for:

• transformations
• aggregations
• reusable logic

---

2. Recursive CTE

Used for hierarchical datasets

Example:

WITH RECURSIVE employee_hierarchy AS (

    SELECT id, manager_id, name
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.manager_id, e.name
    FROM employees e
    JOIN employee_hierarchy eh
    ON e.manager_id = eh.id
)

SELECT *
FROM employee_hierarchy;

Common use cases:

• organization structures
• category trees
• folder hierarchies
• graph traversal

---

Subqueries vs CTEs: Key Differences

Feature	Subqueries	CTEs
Readability	Moderate	High
Reusability	Low	High
Debugging	Harder	Easier
Recursion Support	No	Yes
Best Use Case	Simple filtering	Multi-step logic

---

Performance Comparison

Performance depends on the database system.

General guidance:

Use subqueries when:

• logic is simple
• result used once
• query is short

Use CTEs when:

• logic reused multiple times
• queries become complex
• building layered transformations
• working with hierarchies

Test performance using:

EXPLAIN ANALYZE

This helps evaluate execution plans.

---

When Should You Choose Each?

Choose a subquery if:

• quick filtering needed
• used inside WHERE clause
• logic simple

Choose a CTE if:

• readability matters
• transformation steps needed
• recursion required
• logic reused multiple times

---

Final Thoughts

Both subqueries and CTEs are essential SQL tools for analysts and BI professionals.

Subqueries help embed quick logic inside statements.

CTEs improve structure, readability, and scalability—especially in analytics pipelines and dashboards.

Knowing when to use each makes your SQL cleaner, faster to debug, and easier to maintain.