Back to articles list March 22, 2017 - 4 minutes read Improving Query Readability with Common Table Expressions Marian Dziubiak Junior Technical Writer @ Vertabelo Tags: cte sql how to in sql how to 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. Tags: cte sql how to in sql how to You may also like Get to Know the Power of SQL Recursive Queries Recursive Queries: skewness and kurtosis. Most commonly, the SQL queries we run on a database are quite simple. Read more Subquery vs. CTE: A SQL Primer SQL subqueries and CTEs seem similar, but even pros may not know all their quirks. Learn their similarities, differences, and best use cases. Read more Understanding the Use of NULL in SQL Three-Valued Logic NULLs are necessary in databases, learning to use them is fundamental to SQL success. However, NULLs should be handled with care – see how! Read more How to Use LIKE in SQL: SQL Pattern Matching SQL pattern matching is a very important and useful ability. In this article, we look at how you can match patterns using LIKE in SQL. Read more 5 SQL Functions for Manipulating Strings SQL functions used for manipulating strings are among most important SQL’s tools. Let’s look at five ways you can perform various operations on strings. Read more How to Organize SQL Queries When They Get Long Long queries are very hard for beginners to structure and understand. Learn the best practices for writing and formatting complex SQL code! Read more SQL Statistical Analysis Part 1: Calculating Frequencies and Histograms If you wonder whether you can perform statistical analysis in SQL, the answer is ‘yes’. Read my article to learn how to do this! Read more How Recursive Common Table Expressions Work Recursive Common Table Expressions are immensely useful when you're querying hierarchical data. Let's explore what makes them work. Read more How to Organize SQL Queries with CTEs Common table expressions (CTEs) allow you to structure and organize your SQL queries. It is a necessity when you begin to move deeper into SQL. Read more Simplify SQL Code: Recursive Queries in DBMS Are you repeating the same query in every report? Are they getting too complicated? Use recursive queries to simplify SQL code! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.