Introduction

Structured Query Language (SQL) is a powerful tool for managing and querying databases. One of the most useful yet often underutilized features in SQL is the Common Table Expression (CTE). CTEs provide a way to create temporary result sets that can be referenced within a SQL statement, improving readability and maintainability.

In this post, we will explore what CTEs are, their syntax, benefits, and practical examples to help you use them effectively.

What is a CTE?

A Common Table Expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It helps in breaking down complex queries into smaller, more manageable parts.

A CTE is defined using the WITH keyword followed by the CTE name and a query enclosed in parentheses.

CTE Syntax

Here’s the basic syntax for a CTE:

WITH cte_name (column1, column2, ...) AS (
    -- Your SQL query here
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
-- Using the CTE in a query
SELECT * FROM cte_name;

Example: Using a CTE for Readability

Let’s consider a scenario where we want to find employees who have a salary higher than the average salary.

WITH AvgSalary AS (
    SELECT AVG(salary) AS avg_salary FROM employees
)
SELECT e.employee_id, e.name, e.salary
FROM employees e
JOIN AvgSalary a ON e.salary > a.avg_salary;

Here, the CTE AvgSalary calculates the average salary, and then we use it in the main query to filter employees earning above average.

Benefits of Using CTEs

1. Improves Readability – CTEs make SQL queries easier to read and understand.
2. Encapsulation – Allows breaking down complex queries into modular parts.
3. Recursive Queries – CTEs support recursion, which is useful for hierarchical data (e.g., organizational structures).
4. Reusability – You can use a CTE multiple times in the same query.

---

Recursive CTEs

CTEs also support recursion, which is useful for hierarchical or tree-like data structures.

Example: Finding Employee Hierarchies

WITH EmployeeHierarchy AS (
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

This recursive CTE fetches hierarchical relationships between employees and their managers.