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 Vertabelo Academy’s Common Table Expressions course (coming soon!) if you want to have a go.