Understanding Common Table Expressions (CTE) in SQL — Simplifying Complex Queries

In SQL, we often deal with queries that involve multiple subqueries, aggregations, or recursive lookups.

When these start to get long and unreadable, that’s where CTE (Common Table Expressions) come in.

Let’s break it down from the basics,

What is a CTE?

A Common Table Expression (CTE) is a temporary named result set that exists only within the scope of a single SQL statement.
It’s defined using the WITH keyword, and acts much like a temporary view.

Think of a CTE as a way to store the result of a subquery and reuse it in your main query — while keeping your SQL clean, readable, and modular.

Why Do We Need a CTE?

Without a CTE, you might end up writing deeply nested subqueries that are hard to read or debug.

Here’s what CTEs help with,

Need	Description
Readability	Break down a complex query into smaller, understandable chunks.
Reusability	Reference the same derived dataset multiple times in one query.
Recursive Operations	Ideal for hierarchical or tree-structured data (like org charts).
Debuggability	Easier to test each CTE individually for correctness.

Basic Syntax of CTE


WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name
WHERE column1 > 10;

The CTE is created before the main SELECT (or UPDATE, DELETE, etc.) query, and can be referenced like a temporary table.

Example 1: Simplifying a Subquery

Without CTE


SELECT department_id, COUNT(*)
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Chennai'
)
GROUP BY department_id;

With CTE


WITH chennai_departments AS (
    SELECT department_id
    FROM departments
    WHERE location = 'Chennai'
)
SELECT department_id, COUNT(*)
FROM employees
WHERE department_id IN (SELECT department_id FROM chennai_departments)
GROUP BY department_id;

Easier to read, and we can reuse chennai_departments elsewhere.

Example 2: Multiple CTEs in a Single Query

You can chain multiple CTEs by separating them with commas.


WITH sales_cte AS (
    SELECT salesperson_id, SUM(amount) AS total_sales
    FROM sales
    GROUP BY salesperson_id
),
top_sales_cte AS (
    SELECT salesperson_id
    FROM sales_cte
    WHERE total_sales > 50000
)
SELECT e.name, s.total_sales
FROM employees e
JOIN sales_cte s ON e.id = s.salesperson_id
WHERE e.id IN (SELECT salesperson_id FROM top_sales_cte);

Example 3: Using CTE with UPDATE

CTEs can also be used with UPDATE, DELETE, or INSERT statements.


WITH outdated_products AS (
    SELECT id
    FROM products
    WHERE last_updated < NOW() - INTERVAL '1 year'
)
UPDATE products
SET is_active = FALSE
WHERE id IN (SELECT id FROM outdated_products);

This approach makes bulk updates cleaner and more readable.

CTEs are one of those SQL features that greatly improve query clarity without affecting logic or performance negatively.

If your query feels too long or nested it’s time to introduce a CTE.

They not only make queries more modular and maintainable, but also empower you to handle recursive patterns elegantly.