SQL

Common Table Expressions (CTE) in SQL

📚 A Common Table Expression (CTE) is a temporary named result set that exists only for the duration of a single SQL statement. CTEs improve the readability, organization, and maintainability of complex SQL queries by allowing you to break them into smaller, logical steps.

📖 What is a Common Table Expression (CTE)?

A CTE is created using the WITH keyword, followed by a name and a query enclosed in parentheses. Once defined, the CTE behaves like a temporary table that can be referenced within the main query.

Information

A CTE is temporary and exists only during the execution of the SQL statement in which it is defined. It is not permanently stored in the database.

🎯 Why Use CTEs?

CTEs simplify complex queries by dividing them into smaller, reusable parts. They are especially useful for reporting, recursive queries, and improving query readability.

📌 Improve query readability.
📌 Simplify complex SQL statements.
📌 Eliminate repeated subqueries.
📌 Create recursive queries.
📌 Make SQL easier to maintain and debug.

📝 Basic Syntax

CTE Syntax

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

📊 Sample Table

Consider the following Employees table:

EmployeeID	EmployeeName	Department	Salary
101	Alice	IT	75000
102	Bob	HR	55000
103	Charlie	Finance	68000
104	David	IT	82000
105	Emma	HR	60000

💡 Basic CTE Example

Create a CTE that stores employees earning more than 70000, then retrieve the results.

Simple CTE

WITH HighSalaryEmployees AS
(
    SELECT EmployeeID,
           EmployeeName,
           Salary
    FROM Employees
    WHERE Salary > 70000
)
SELECT *
FROM HighSalaryEmployees;

📊 CTE with Aggregate Functions

Use a CTE to calculate average salaries for each department.

Department Average Salary

WITH DepartmentAverage AS
(
    SELECT Department,
           AVG(Salary) AS AverageSalary
    FROM Employees
    GROUP BY Department
)
SELECT *
FROM DepartmentAverage;

🔍 CTE with JOIN

CTEs can simplify queries involving joins by separating complex logic into readable steps.

CTE with JOIN

WITH EmployeeCourses AS
(
    SELECT e.EmployeeName,
           c.CourseName
    FROM Employees e
    INNER JOIN Courses c
    ON e.EmployeeID = c.EmployeeID
)
SELECT *
FROM EmployeeCourses;

📈 Multiple CTEs

SQL allows you to define multiple CTEs in a single query. Separate each CTE with a comma.

Multiple CTEs

WITH DepartmentAverage AS
(
    SELECT Department,
           AVG(Salary) AS AverageSalary
    FROM Employees
    GROUP BY Department
),
HighSalaryEmployees AS
(
    SELECT EmployeeName,
           Department,
           Salary
    FROM Employees
    WHERE Salary > 70000
)
SELECT h.EmployeeName,
       h.Department,
       d.AverageSalary
FROM HighSalaryEmployees h
JOIN DepartmentAverage d
ON h.Department = d.Department;

🔁 Recursive CTE

A recursive CTE references itself and is commonly used to work with hierarchical or tree-structured data such as employee-manager relationships, organizational charts, or category hierarchies.

Recursive CTE Example

WITH RECURSIVE EmployeeHierarchy AS
(
    SELECT EmployeeID,
           EmployeeName,
           ManagerID
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.EmployeeID,
           e.EmployeeName,
           e.ManagerID
    FROM Employees e
    JOIN EmployeeHierarchy h
    ON e.ManagerID = h.EmployeeID
)
SELECT *
FROM EmployeeHierarchy;

Important

Some database systems, such as PostgreSQL and SQLite, require the RECURSIVE keyword for recursive CTEs, while others, such as SQL Server, infer recursion automatically when a CTE references itself.

⚖️ CTE vs Subquery

Feature	CTE	Subquery
Readability	Excellent for complex queries.	Can become difficult to read.
Reusability	Can be referenced multiple times within the same query.	Often repeated if needed multiple times.
Recursive Support	Yes.	No.
Scope	Single SQL statement.	Limited to where it is written.

💼 Real-World Example

A company wants to display employees whose salary is above their department's average salary.

Department Salary Analysis

WITH DepartmentAverage AS
(
    SELECT Department,
           AVG(Salary) AS AverageSalary
    FROM Employees
    GROUP BY Department
)
SELECT e.EmployeeName,
       e.Department,
       e.Salary,
       d.AverageSalary
FROM Employees e
JOIN DepartmentAverage d
ON e.Department = d.Department
WHERE e.Salary > d.AverageSalary
ORDER BY e.Department,
         e.Salary DESC;

This query first calculates the average salary for each department using a CTE, then compares each employee's salary against the department average.

⚠️ Common Mistakes

❌ Forgetting the WITH keyword.
❌ Trying to use a CTE outside the SQL statement where it is defined.
❌ Assuming a CTE permanently stores data.
❌ Omitting a termination condition in recursive CTEs, which can cause infinite recursion or recursion limit errors.

Warning

A CTE is not a permanent database object. If you need to reuse the same data across multiple SQL statements or sessions, consider using a view or a temporary table instead.

⚠️ Best Practices

Best Practice

Use CTEs to improve readability and organize complex queries into logical steps. Choose descriptive CTE names, avoid unnecessary nesting, use recursive CTEs only when appropriate, and consider query performance when working with very large datasets or multiple CTEs.

🚀 Key Points to Remember

📌 A CTE is created using the WITH keyword.
📌 It behaves like a temporary named result set.
📌 A CTE exists only for the duration of a single SQL statement.
📌 CTEs improve query readability and maintainability.
📌 Multiple CTEs can be defined in one query.
📌 Recursive CTEs are useful for hierarchical and tree-structured data.

>>"A Common Table Expression transforms complex SQL into clear, structured, and maintainable logic."

Summary

✅ Common Table Expressions (CTEs) are a powerful SQL feature that makes complex queries easier to read, write, and maintain. They are ideal for breaking queries into logical steps, replacing repeated subqueries, and solving hierarchical problems with recursive queries.