Back to articles list October 16, 2017 - 5 minutes read How to Organize SQL Queries with CTEs Aldo Zelen Aldo is a data architect with a passion for the cloud. From leading a team of data professionals to coding a data warehouse in the cloud, Aldo has experience with the whole lifecycle of data-intensive projects. Aldo spends his free time forecasting geopolitical events in forecasting tournaments. Tags: common table expressions CTE recursive queries WITH 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: SUM_SALES_REVENUE SALES_ITEM_ID 202 100 242 80 408 40 700 2 1100 20 2150 600 2755 10 2959 200 3531 8 5147 50 5424 25 17710 6 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: SALES_CUSTOMER_ID sales_percent SALES_ITEM_ID 1 100 100 3 50 40 1 74 25 3 53 50 3 100 8 2 100 2 2 47 50 2 50 6 3 25 25 2 100 20 1 99 200 2 1 25 3 50 6 2 1 200 1 100 10 2 100 600 2 100 80 2 50 40 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: CUSTOMER_NAME sales_percent ITEM_NAME Big Co 99 Dollhouses Medium Co 1 Dollhouses Medium Co 100 Legos Medium Co 100 Model Airplanes Small Co 100 Model Cars Medium Co 100 Pens Medium Co 100 Pins Medium Co 50 Rugs Small Co 50 Rugs 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 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! Tags: common table expressions CTE recursive queries WITH You may also like 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 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 Long SQL Query vs. Recursive SQL Query Explore recursive CTE hierarchy in SQL server and find out how to tune your SQL query performance. Read more An Introduction to Using SQL Aggregate Functions with JOINs Aggregate functions. Powerful SQL tools. Let's see how they cooperate paired with LEFT JOIN, SUM and GROUP BY perform computations on multiple tables. 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 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 Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.