How to Organize SQL Queries with CTEs

Common table expressions (CTEs) allow you to structure and organize SQL queries. Knowing how to organize SQL 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 SQL 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 in SQL, 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 SQL CTE and explain how to organize SQL queries using them. 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 LearnSQL.com SQL CTE course for some interactive exercises.

Getting to Know CTEs

In its basic form, a SQL 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 SQL queries for better readability or performance.

Let’s consider an SQL CTE example for code organization. We’ll start by looking at three tables containing information about “sales”, “customers” and sale “items”.

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)

In our SQL CTE example, 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.

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 SQL CTE example 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. When you use a CTE in SQL, it’s 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 SQL CTE is:

Finally, we will add customer and item names to the result table in our SQL CTE example. We do this by joining our customer_sales CTE to the “customer” and “items” tables.

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:

Now that you’ve been introduced to the topic of the CTE in SQL, 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, improve your skills with LearnSQL.com Recursive Queries course!