Improving Query Readability with Common Table Expressions

What is a Common Table Expression, or CTE? Where do you use them, and why? This post answers your questions.

Simply put, Common Table Expressions (also known as WITH clauses) are essentially named subqueries. They also provide additional features like recursion. If you're new to subqueries, I recommend you read the SQL Subqueries article before continuing.

The main purpose of Common Table Expressions is to improve the design and readability of an SQL statement. Obviously, you should choose your subquery names with care. But when you use CTEs properly, they have a lot of benefits. They allow you to separate different parts of your query into easily-comprehensible subqueries, which improves the quality of the code. Also, by eliminating repetition, your work gets easier and your comprehension is quicker. For example, if you have the same subquery in two places, whenever you change one you have to change the other. With CTEs, that problem is gone.

CTE Scope and Syntax

CTEs are declared by placing a WITH clause above a SELECT, INSERT, UPDATE, or DELETE statement. You could say CTEs function like a temporary view. Because CTEs are like subqueries, they must be part of an SQL statement or query. You can't declare a CTE on its own.

The declaration of a Common Table Expression is pretty straightforward:

WITH CTE_name [ (column_name [, ...]) ]
AS (
	SELECT ...
)
statement

You can also create multiple CTEs by separating them with a comma – there's no need to repeat the WITH. A CTE can also reference another CTE that's been previously defined in the same statement.

CTE Usage Examples

CTEs are usually used in complex queries that would involve a lot of code repetitions or that would be hard to do with subqueries. However, we'll start with a basic example to demonstrate how they work.

First, we'll declare a simple table:

CREATE TABLE Cars (
	Id INT PRIMARY KEY,
	Name VARCHAR(45) NOT NULL,
	Year INT NOT NULL)

And populate it with some values:

INSERT INTO Cars VALUES	(1, 'Prius', 2005),
                        (2, 'Mitsubishi', 2010),
                        (3, 'Volkswagen', 2007),
                        (4, 'Mazda', 2001)

If we wanted to get the names of cars made after 2006 using a subquery, we could use this query:

SELECT Name
FROM (SELECT * FROM Cars WHERE Year > 2006)

The same result can be achieved with this Common Table Expression:

WITH new_cars
AS (
	SELECT * 
	FROM Cars 
	WHERE Year > 2006
)
SELECT Name FROM new_cars

Of course, with such a simple query, we wouldn't ordinarily use a CTE. Let's look at a more realistic example. Suppose we have the following tables:

     Employees                       Bosses
id          name             employee_id  boss_id
----------- ------------     ------------ -----------
          1 Harry                       1 2
          2 John                        3 2
          3 Sebastian                   2 4
          4 Mick

We need to know which employee has a boss that doesn't have a boss. I am determined to use this subquery:

SELECT e1.name, e2.name
FROM Employees e1
JOIN Bosses ON (e1.id = Bosses.employee_id)
JOIN Employees e2 ON (Bosses.boss_id = e2.id)

Now I need to reference this subquery twice in my query, so I will make it into a CTE.

WITH Bosses_Named
AS (
	SELECT e1.name AS ename, e2.name AS bname
	FROM Employees e1
	JOIN Bosses ON (e1.id = Bosses.employee_id)
	JOIN Employees e2 ON (Bosses.boss_id = e2.id)
)
SELECT ename
FROM Bosses_Named
WHERE bname NOT IN (SELECT ename FROM Bosses_Named)

If it wasn't for the CTE, I'd have to copy my subquery to both FROM clauses. This is the same query without the CTE:

SELECT ename
FROM (
	SELECT e1.name AS ename, e2.name AS bname
	FROM Employees e1
	JOIN Bosses ON (e1.id = Bosses.employee_id)
	JOIN Employees e2 ON (Bosses.boss_id = e2.id) )
WHERE bname NOT IN (
					SELECT ename FROM (
						SELECT e1.name AS ename, e2.name AS bname
						FROM Employees e1
						JOIN Bosses ON (e1.id = Bosses.employee_id)
						JOIN Employees e2 ON (Bosses.boss_id = e2.id) )
					)

As you can see, it's much longer and more involved. That's where CTEs are really awesome – they save you a lot of time and confusion.

Learn More About CTEs

To get a better grip on CTEs, I'd suggest practicing queries and learning the recursive features for yourself. You can check out LearnSQL's Common Table Expressions course if you want to have a go.