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.