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

{% raw %}

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_nameWHERE 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 employeesWHERE 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 employeesWHERE 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_salesFROM employees eJOIN sales_cte s ON e.id = s.salesperson_idWHERE 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 productsSET is_active = FALSEWHERE 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.

{% endraw %}