SQL - Common Table Expressions (CTEs) in SQL

1. What is a Common Table Expression?

A Common Table Expression (CTE) is a temporary named result set that you define within a SQL query.
It allows you to break complex queries into smaller, more readable parts.

A CTE exists only while the query is running and does not permanently store data in the database.

In simple terms, a CTE acts like a temporary table that helps organize complicated SQL statements.

2. Why Use CTEs?

  • Makes queries easier to read and maintain

  • Helps break complex logic into steps

  • Avoids repeating the same subquery multiple times

  • Useful for hierarchical or recursive queries

  • Improves query structure and clarity

3. Basic Syntax

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

Explanation:

  • WITH defines the CTE

  • cte_name is the temporary name

  • The main query uses it like a table

4. Example

Suppose you want to find employees earning above the average salary.

Without CTE:

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

With CTE:

WITH avg_salary AS ( SELECT AVG(salary) AS avg_sal FROM employees ) SELECT * FROM employees, avg_salary WHERE employees.salary > avg_salary.avg_sal;

This version separates the logic and improves readability.

5. Recursive CTEs

CTEs can reference themselves, which is useful for hierarchical data like organizational charts or folder structures.

Example structure:

WITH RECURSIVE hierarchy AS ( SELECT id, parent_id FROM table_name WHERE parent_id IS NULL UNION ALL SELECT t.id, t.parent_id FROM table_name t JOIN hierarchy h ON t.parent_id = h.id ) SELECT * FROM hierarchy;

This repeatedly builds levels of related data.

6. Difference Between CTE and Subquery

CTE

  • Named temporary result

  • Easier to read

  • Can be reused within query

  • Supports recursion

Subquery

  • Written inside another query

  • Harder to read when complex

  • Typically used once

  • No recursion

Summary

Common Table Expressions help simplify complex SQL queries by allowing you to define temporary result sets that can be referenced within the same query. They improve readability, support reuse, and enable recursive operations, making them a valuable tool for managing advanced SQL logic.