Common Table Expressions (CTE) – SQL
(Simplifying Subqueries with CTEs)
- Common Table Expressions (CTEs) is a powerful technique for simplifying complex queries, improving readability, and enabling recursive logic.
- CTEs define a temporary, named result set that exists only for the duration of a single query.
Syntax
A CTE is always introduced by the WITH keyword, followed by the CTE’s name, the AS keyword, and the defining SELECT statement enclosed in parentheses.
1. Single CTE
The most basic structure involves a single CTE followed by the main query that references it:
WITH cte_name (column1, column2, ...) AS ( -- Column list is optional
-- The defining SELECT statement (the temporary result set)
SELECT col_a, col_b
FROM source_table
WHERE condition
)
-- The main query that uses the CTE
SELECT column1, column2
FROM cte_name
WHERE another_condition;
Examples
Let’s use a sample table called employees for these examples:
+-------------+----------+----------------+--------+------------+
| employee_id | name | department_id | salary | manager_id |
+-------------+----------+----------------+--------+------------+
| 101 | Alice | 1 | 70000 | 104 |
| 102 | Bob | 2 | 65000 | 104 |
| 103 | Charlie | 1 | 80000 | 105 |
| 104 | David | 3 | 95000 | NULL |
| 105 | Eve | 2 | 75000 | 104 |
| 106 | Fiona | 3 | 110000 | 105 |
+-------------+----------+----------------+--------+------------+
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(50),
department_id INT,
salary INT,
manager_id INT
);
INSERT INTO employees (employee_id, name, department_id, salary, manager_id) VALUES
(101, 'Alice', 1, 70000, 104),
(102, 'Bob', 2, 65000, 104),
(103, 'Charlie', 1, 80000, 105),
(104, 'David', 3, 95000, NULL),
(105, 'Eve', 2, 75000, 104),
(106, 'Fiona', 3, 110000, 105);
Instead of using a non-reusable subquery in the FROM clause, a CTE provides a clear name for the logic.
Goal: Find all employees whose salary is above the average salary for their respective department.
WITH department_average AS (
-- CTE to calculate the average salary for each department
SELECT department_id, AVG(salary) AS avg_department_salary
FROM employees
GROUP BY department_id
)
SELECT e.name, e.salary, d.department_average
FROM employees e
JOIN department_average d ON e.department_id = d.department_id
WHERE e.salary > d.avg_department_salary;
+---------+--------+-----------------------+
| name | salary | avg_department_salary |
+---------+--------+-----------------------+
| Charlie | 80000 | 75000.0000 |
| Eve | 75000 | 70000.0000 |
| Fiona | 110000 | 102500.0000 |
+---------+--------+-----------------------+
3 rows in set (0.143 sec)
Without CTE: This would require calculating the department_average in a subquery and joining to it, or using an expensive correlated subquery in the WHERE clause. The CTE makes the logic easy to follow: first, get the average, then use it to filter the employees.
SELECT e.name, e.salary, d.avg_department_salary
FROM employees e
JOIN (
SELECT department_id, AVG(salary) AS avg_department_salary
FROM employees
GROUP BY department_id
) d
ON e.department_id = d.department_id
WHERE e.salary > d.avg_department_salary;
2. Multiple CTE Syntax
You can chain multiple CTEs together. Subsequent CTEs can refer to previously defined CTEs within the same WITH clause. The CTEs are separated by commas.
WITH first_cte AS (
-- Defines the first result set
SELECT ...
),
second_cte AS (
-- Defines the second result set, referencing the first_cte
SELECT *
FROM first_cte
WHERE ...
),
third_cte (c3_col1, c3_col2) AS (
-- Defines the third result set
SELECT ...
)
-- Main query referencing any of the defined CTEs
SELECT *
FROM second_cte, third_cte
WHERE ...;
Example
CTEs are excellent for multi-step processing where the output of one step is the input for the next.
Goal: First, get the list of high-earning managers (salary > 90000), and then find all employees who report to those specific high-earning managers.
WITH high_earning_managers AS (
-- Step 1: Identify managers earning $90,000 or more
SELECT employee_id AS manager_id, name AS manager_name
FROM employees
WHERE salary >= 90000
),
employee_details AS (
-- Step 2: Join HighEarningManagers with the Employees table
-- to find their direct reports.
SELECT e.name AS employee_name, e.salary AS employee_salary, m.manager_name
FROM employees e
JOIN high_earning_managers m ON e.manager_id = m.manager_id
)
-- Final Query: Select the required information
SELECT employee_name, employee_salary, manager_name
FROM employee_details
ORDER BY manager_name, employee_salary DESC;
+---------------+-----------------+--------------+
| employee_name | employee_salary | manager_name |
+---------------+-----------------+--------------+
| Eve | 75000 | David |
| Alice | 70000 | David |
| Bob | 65000 | David |
+---------------+-----------------+--------------+
Clarity: The chained CTEs clearly delineate the two logical steps: finding the managers, then finding their reports.
--- without CTE
SELECT
employee_name,
employee_salary,
manager_name
FROM (
-- employee_details replacement
SELECT
e.name AS employee_name,
e.salary AS employee_salary,
m.manager_name
FROM employees e
JOIN (
-- high_earning_managers replacement
SELECT
employee_id AS manager_id,
name AS manager_name
FROM employees
WHERE salary >= 90000
) m
ON e.manager_id = m.manager_id
) AS employee_details
ORDER BY manager_name, employee_salary DESC;
Key Rules and Considerations
- Scope:
A CTE is temporary and can only be used within the single SELECT, INSERT, UPDATE, DELETE, or MERGE statement immediately following the WITH clause. It cannot be referenced by other, separate queries. - No Indexing:
Since a CTE is not physically stored, you cannot create indexes on it. - Performance vs. Readability:
While CTEs are highly valuable for readability and complexity management, they do not necessarily improve query performance over an equivalent subquery. In most modern SQL systems, the query optimizer treats CTEs and subqueries similarly. If a CTE is referenced multiple times, however, some database systems may re-execute the CTE’s defining query for each reference (unless materialized or optimized otherwise), which could hurt performance. - Data Modification:
You can use a CTE to define the rows to be affected by an INSERT, UPDATE, or DELETE statement, making complex modifications clearer.
-- Example: Delete employees who report to a specific manager
WITH EmployeesToDelete AS (
SELECT EmployeeID
FROM Employees
WHERE
ManagerID = 104
)
DELETE FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID FROM EmployeesToDelete);
Exercise
1:
+------------+--------------------------+-------------+---------------------+------------+----------------+------------+
| product_id | product_name | category_id | parent_category_id | unit_price | units_in_stock | units_sold |
+------------+--------------------------+-------------+---------------------+------------+----------------+------------+
| 101 | laptop_x1 | 1 | NULL | 1200.00 | 50 | 20 |
| 102 | gaming_mouse | 2 | 1 | 75.00 | 150 | 80 |
| 103 | mechanical_keyboard | 2 | 1 | 120.00 | 90 | 45 |
| 104 | 4k_monitor | 3 | NULL | 450.00 | 30 | 15 |
| 105 | usb_c_hub | 4 | 3 | 40.00 | 200 | 110 |
| 106 | software_license_pro | 5 | NULL | 300.00 | 5 | 20 |
| 107 | office_chair_ergonomic | 6 | NULL | 350.00 | 40 | 10 |
| 108 | desk_mat_large | 6 | 107 | 25.00 | 180 | 50 |
| 109 | webcam_hd | 2 | 1 | 55.00 | 110 | 30 |
| 110 | portable_ssd_1tb | 4 | 3 | 150.00 | 70 | 35 |
+------------+--------------------------+-------------+---------------------+------------+----------------+------------+
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category_id INT,
parent_category_id INT,
unit_price DECIMAL(10, 2),
units_in_stock INT,
units_sold INT
);
INSERT INTO products (product_id, product_name, category_id, parent_category_id, unit_price, units_in_stock, units_sold) VALUES
(101, 'laptop_x1', 1, NULL, 1200.00, 50, 20),
(102, 'gaming_mouse', 2, 1, 75.00, 150, 80),
(103, 'mechanical_keyboard', 2, 1, 120.00, 90, 45),
(104, '4k_monitor', 3, NULL, 450.00, 30, 15),
(105, 'usb_c_hub', 4, 3, 40.00, 200, 110),
(106, 'software_license_pro', 5, NULL, 300.00, 5, 20),
(107, 'office_chair_ergonomic', 6, NULL, 350.00, 40, 10),
(108, 'desk_mat_large', 6, 107, 25.00, 180, 50),
(109, 'webcam_hd', 2, 1, 55.00, 110, 30),
(110, 'portable_ssd_1tb', 4, 3, 150.00, 70, 35);
-- Find the product_name and unit_price of all products whose price is greater than the overall average unit price of all products.
-- using sub-query
SELECT product_name, unit_price
FROM products
WHERE unit_price > (
SELECT AVG(unit_price)
FROM products
);
-- using CTE
WITH avg_unit_price AS (
SELECT AVG(unit_price) AS avg_unit_price
FROM products
)
SELECT p.product_name, p.unit_price
FROM products p
CROSS JOIN avg_unit_price a
WHERE p.unit_price > a.avg_unit_price;
-- by category
WITH avg_unit_price AS(
SELECT category_id, AVG(unit_price) avg_unit_price
FROM products
GROUP BY category_id
)
SELECT p.product_name, p.unit_price, a.avg_unit_price
FROM products p
JOIN avg_unit_price a ON p.category_id = a.category_id
WHERE p.unit_price > a.avg_unit_price;