Understanding Subqueries and CTEs in SQL: A Complete Guide

Working with relational databases often requires breaking down complex problems into manageable parts. Two powerful tools that help achieve this in SQL are subqueries and Common Table Expressions (CTEs). While they may seem similar at first, they serve different purposes and are best used in different scenarios.

This article explores what subqueries and CTEs are, their types, use cases, and how they compare in terms of performance and readability.

What is a Subquery?

A subquery is a query nested inside another SQL query. It is used to perform operations that depend on the result of another query.

Basic Example

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

In this example:

The inner query calculates the average salary.
The outer query retrieves employees earning above that average.

👉 In simple terms, a subquery provides intermediate results to the main query.

Types of Subqueries

Subqueries can be categorized based on how they are used and how they interact with the outer query.

1. Single-row Subquery

Returns only one row.

SELECT name
FROM employees
WHERE department_id = (
    SELECT id FROM departments WHERE name = 'Sales'
);

1. Multi-row Subquery

Returns multiple rows and is used with operators like IN, ANY, or ALL.

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

1. Correlated Subquery

Depends on the outer query and is executed once for each row.

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

👉 This is more dynamic but can be slower due to repeated execution.

1. Nested Subquery

A subquery inside another subquery.

SELECT name
FROM employees
WHERE department_id = (
    SELECT id
    FROM departments
    WHERE location = (
        SELECT location
        FROM offices
        WHERE city = 'Nairobi'
    )
);

When Should Subqueries Be Used?

Subqueries are ideal when:

You need a value derived from another query
The logic is simple and contained
You want to filter results dynamically
You’re working with aggregates (AVG, MAX, MIN, etc.)

However, they can become inefficient or hard to read when deeply nested or correlated.

What are CTEs (Common Table Expressions)?

A Common Table Expression (CTE) is a temporary result set defined at the beginning of a query using the WITH keyword. It improves readability and organization, especially in complex queries.

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

👉 Think of a CTE as a temporary named query you can reference within your main query.

Types and Use Cases of CTEs

1. Non-Recursive CTE

The most common type, used for simplifying complex queries.

WITH department_totals AS (
    SELECT department_id, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department_id
)
SELECT *
FROM department_totals
WHERE total_salary > 50000;

Use case:

Breaking down large queries into readable parts

1. Recursive CTE

Used to handle hierarchical or tree-structured data.

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

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

Use case:

Organizational charts
Category hierarchies
Graph traversal
When Should CTEs Be Used?

CTEs are best when:

Queries are complex and need structure
You want to reuse a result multiple times
You need recursive logic
You want to improve readability and maintainability
Subqueries vs CTEs: A Clear Comparison

1. Readability Subqueries: Can become difficult to read when nested CTEs: Much cleaner and easier to understand

👉 Winner: CTEs

1. Performance Subqueries: Correlated subqueries can be slow Often re-executed multiple times CTEs: Sometimes optimized better by the database But in some systems, they may not be cached and can behave like inline views

👉 Winner: Depends on the database engine

For repeated logic → CTEs often better
For simple tasks → subqueries are fine

1. Reusability Subqueries: Cannot be reused easily CTEs: Can be referenced multiple times in the same query

👉 Winner: CTEs

1. Complexity Handling Subqueries: Good for simple conditions CTEs: Ideal for complex, multi-step logic

👉 Winner: CTEs

1. Recursion Subqueries: Cannot handle recursion CTEs: Support recursive queries

👉 Winner: CTEs

When to Use Each
Use Subqueries when:
The query is simple and short
You only need the result once
You’re filtering using aggregates
Use CTEs when:
The query is complex or layered
You need better readability
You want to reuse logic
You’re working with hierarchical data
Conclusion

Both subqueries and CTEs are essential tools in SQL, and understanding when to use each can significantly improve your queries.

Subqueries are concise and useful for straightforward operations
CTEs provide structure, clarity, and power for more advanced scenarios

In practice, experienced developers often prefer CTEs for maintainability, especially in large projects—but subqueries still have their place for quick, simple tasks.