Choosing a CTE over a subquery in SQL depends on many factors: readability, reusability, specific use cases, performance and personal preference. Let’s check out each.
Terminology
A Common Table Expression (CTE) in SQL is a named temporary result set that can be referenced within a larger query. It allows for better readability and reusability by breaking down complex queries into smaller, more manageable parts. CTEs are particularly useful for recursive queries, aggregations, or when a result set needs to be used multiple times within a query. A query using a CTE looks like this:
-- displaying a filtered result set
WITH CTEExample AS (
SELECT *
FROM Employees
WHERE DepartmentID = 1
)
SELECT *
FROM CTEExample;A subquery is a nested query enclosed within parentheses and typically used to retrieve or manipulate data within another query’s context, often in situations where a single result set or value is needed for comparison or filtering. A query using a subquery looks like this:
-- retrieves all employees who belong to the 'Sales' department
SELECT *
FROM Employees
WHERE DepartmentID = (
SELECT DepartmentID
FROM Departments
WHERE DepartmentName = 'Sales'
);Readability — Complex queries
If you have a query that is starting to get a bit more complex, it is better to break it down into simpler, easily manageable bits using CTEs since they improve readability and they are more straightforward. You can also save yourself a lot of trouble when you need to revisit the same query months from now.
For the sake of simplicity, let’s say we have the following task: we want to identify employees who earn more than the average salary within their departments. We have a table Employee with EmployeeID, Name, DepartmentId and Salary in our database. A solution with CTE would be:
-- CTE to calculate the average salary per department
WITH DeptAvgSalaries AS (
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY DepartmentID
)
-- filter employees who earn more than the average salary in their department
SELECT
e.EmployeeID,
e.Name,
e.DepartmentID,
d.AvgSalary,
e.Salary,
e.Salary - d.AvgSalary AS SalaryDiff
FROM Employee e
JOIN DeptAvgSalaries d
ON e.DepartmentID = d.DepartmentID
WHERE e.Salary > d.AvgSalary;Subqueries can be harder to read and understand, especially when nested deeply:
SELECT
e.EmployeeID,
e.Name,
e.DepartmentID,
(SELECT AVG(Salary) FROM Employee WHERE DepartmentID = e.DepartmentID) AS AvgSalary,
e.Salary,
e.Salary - (SELECT AVG(Salary) FROM Employee WHERE DepartmentID = e.DepartmentID) AS SalaryDiff
FROM Employee e
WHERE e.Salary > (SELECT AVG(Salary) FROM Employee WHERE DepartmentID = e.DepartmentID);To me query 1 looks cleaner. What is your opinion?
Reusability
Sometimes you need to reuse the result set. Subqueries are inline and therefore cannot be reused within the same query.
Let’s say that you want to find employees who earn more than the average salary in their department and display their EmployeeID, Name, DepartmentID. Also, you want to show the average salary of their department next to their information and calculate and display the difference between each employee’s salary and the average salary of their department.
We again have a table Employee with EmployeeID, Name, DepartmentId and Salary.
-- Define the CTE to calculate the average salary per department
WITH DeptAvgSalaries AS (
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY DepartmentID
)
-- CTE is used to filter employees who earn more than the average salary in their department
SELECT
e.EmployeeID,
e.Name,
e.DepartmentID,
d.AvgSalary
FROM Employee e
JOIN DeptAvgSalaries d
ON e.DepartmentID = d.DepartmentID
WHERE e.Salary > d.AvgSalary
UNION ALL
-- CTE is reused to show the average salary of each department only
SELECT
NULL AS EmployeeID,
NULL AS Name,
d.DepartmentID,
d.AvgSalary
FROM DeptAvgSalaries d
ORDER BY DepartmentID;Specific use cases — Recursion
CTEs are the only option when it comes to recursion because subqueries do not support it. The day-to-day use of recursion in SQL development is limited to specific use cases, like when you need to traverse and query hierarchical or recursive data structures. Recursions seem to be way more popular on exams, than in real life. I only needed to do recursion once for work, as part of an interview. If I attempted to use pure SQL recursion at work, I would be stopped quickly because it is not straightforward for the majority. I agree, there is a learning curve.
Let’s say we want to retrieve all subordinates (direct and indirect) under a specific manager and display the hierarchical structure by showing each employee along with their level in the hierarchy:
-- recursive CTE that retrieves all subordinates
WITH RecursiveEmployees AS (
-- Anchor member: select the manager and level 0
SELECT EmployeeID, Name, ManagerID, 0 AS Level
FROM Employee
WHERE EmployeeID = 1 -- the EmployeeID of the manager (starting point)
UNION ALL
-- Recursive member: select direct subordinates and increment level
SELECT e.EmployeeID, e.Name, e.ManagerID, Level + 1
FROM Employee e
JOIN RecursiveEmployees re ON e.ManagerID = re.EmployeeID
)
-- Query the recursive CTE to get hierarchical data
SELECT EmployeeID, Name, Level
FROM RecursiveEmployees
ORDER BY Level, EmployeeID;Performance
Performance always depends on the DBMS and the query itself. However, sometimes you will get an improvement in performance if you switch one with the other. I can only recommend to use EXPLAIN ANALYZE and see why CTE would perform better in your query.
Personal Preference
I like to use CTE in most cases because they are more straightforward. Subqueries are not always easy to read, and requires me to be “in the zone”. Sometimes it is shorter and more cost-effective to use subqueries over CTEs. That is when I go for a subquery.
Familiarity and comfort level play a giant role in my choice between CTEs and subqueries. This is probably going to be the same for you.
Maybe I prefer CTEs because when I see a query loaded with subqueries, it reminds me my early days of programming, when I was trying to wrap my head around deeply nested JavaScript callbacks.
Remember, SQL queries are like puzzles — sometimes frustrating, often rewarding, but always worth the effort.
May your queries be forever optimized!
