Common table expressions (CTEs) allow you to structure and organize your SQL code. Being able to write organized queries is a necessity when you begin to move deeper into SQL, so if you want to become an SQL master, you need to know CTEs.
The CTE has been part of standard SQL for some time now. CTEs – which are also called WITH statements – are available in all major RDBMS. When you use a CTE, it’s quicker and easier to write code. So let’s introduce ourselves to the Common Table Expression in SQL.
The goal of this article is to show you the basics of the CTE. But you won’t be an expert; you’ll need more experience and hands-on learning for that. I suggest that after reading this article you check out the Vertabelo Academy CTE course for some interactive exercises.
Getting to Know the CTE
In its basic form, a CTE is a named temporary result set within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs replace subqueries, views, and inline user-defined functions.
SQL developers use CTEs to create hierarchical queries and to re-factor and organize their SQL code for better readability or performance.
Let’s consider an example of using a CTE for code organization. We’ll start by looking at three tables containing information about “
customers” and sale “
For every row in the “
sales” table, we know:
- The salesperson who sold the item(s)
- The number of items sold in the transaction
- The price per each item
- The time of the sale
- The customer who bought the item(s)
We would like to know the percentage of each item’s revenue for different customers, or how one item has been distributed to different customers.
First, we define the total revenue per item:
SELECT sum(sales_num*sales_price) sum_sales_revenue, sales_item_id FROM sales GROUP BY sales_item_id ORDER BY sum_sales_revenue
The result set is of this query is:
Now let’s say that we define this result set as
summary_sales. Instead of creating a view or an intermediary table, we use a CTE to define this virtual grouping:
WITH summary_sales AS ( SELECT sum(sales_num*sales_price) sum_sales_revenue, sales_item_id FROM sales GROUP BY sales_item_id ORDER BY sum_sales_revenue) SELECT * FROM summary_sales
Notice the syntax: the WITH keyword and the CTE name with the AS keyword. In parentheses, we see the query. The CTE is queryable, just like a normal table; you can see it right after the closing bracket.
Now let’s calculate the percentage of every item sold to each customer by total revenue to that customer. We accomplish this by defining a new CTE group called
customers_sales. The new group, since it is part of a CTE statement, does not need a new WITH clause. We just chain it under the previous group:
WITH sumary_sales AS ( SELECT sum(sales_num*sales_price) sum_sales_revenue, sales_item_id FROM sales GROUP BY sales_item_id ORDER BY sum_sales_revenue ), customers_sales AS ( SELECT sales_customer_id, round(sum(sales_num*sales_price)/sum_sales_revenue,2)*100 sales_percent, sumary_sales.sales_item_id FROM sales JOIN sumary_sales ON sumary_sales.sales_item_Id = sales.sales_item_id GROUP BY sales_customer_id, sumary_sales.sales_item_id, sum_sales_revenue ) SELECT * FROM customers_sales
The result of this CTE is:
Finally, we will add customer and item names to the result table. We do this by joining our
customer_sales CTE to the “
customer” and “
WITH sumary_sales AS ( SELECT sum(sales_num*sales_price) sum_sales_revenue, sales_item_id FROM sales GROUP BY sales_item_id ORDER BY sum_sales_revenue ), customers_sales AS ( SELECT sales_customer_id, round(sum(sales_num*sales_price)/sum_sales_revenue,2)*100 sales_perchent, sumary_sales.sales_item_id FROM sales JOIN sumary_sales ON sumary_sales.sales_item_Id = sales.sales_item_id GROUP BY sales_customer_id, sumary_sales.sales_item_id, sum_sales_revenue ) SELECT customer_name, sales_perchent, items.item_name FROM customers_sales JOIN customers ON customers.id = sales_customer_id JOIN items ON items.id = sales_item_id ORDER BY item_name
Here is the final result set:
|Medium Co||100||Model Airplanes|
|Small Co||100||Model Cars|
|Medium Co||50||Toy Airplanes|
|Small Co||50||Toy Airplanes|
|Big Co||100||Toy Cars|
|Big Co||74||Toy Dolls|
|Medium Co||1||Toy Dolls|
|Small Co||25||Toy Dolls|
|Medium Co||47||Toy Kitchen|
|Small Co||53||Toy Kitchen|
|Big Co||100||Toy Tools|
Now that you’ve been introduced to SQL’s Common Table Expressions, it’s time to practice. The
WITH statement is a precursor to recursion, which is a useful and interesting feature of SQL. Why not revisit any of your old queries that have many subqueries and rewrite them using CTEs? And if you don’t feel ready for doing it on your own yet, give Vertabelo Academy Recursive Queries course a try!