What are common table expressions (CTE) in SQL?
Common table expressions (CTEs) are a powerful SQL feature that allows us to define temporary result sets (often referred to as “common tables”) within a SQL query. CTEs make complex queries more readable, maintainable, and easier to work with by breaking them into smaller, more manageable pieces.
Here’s the basic syntax of a CTE:
WITH cte_name (column1, column2, ...) AS (-- SQL query that defines the CTE)-- The main query that uses the CTESELECT ...FROM cte_nameWHERE ...
Let’s break down the components used in the syntax:
Line 1: The
WITHkeyword indicates the start of a CTE definition. Thecte_nameis the name we give to the CTE. It’s used to reference the temporary result set in the main query. The(column1, column2, ...)optional column names can be specified, similar to creating a temporary table. These are optional and are used to alias the columns of the CTE. TheASkeyword separates the CTE definition from the actual SQL query that generates the result set.Lines 1–3: The SQL query within the parentheses is the query that defines the CTE. It can be a simple
SELECTstatement or a more complex query. The result of this query becomes the temporary result set that we can reference in the main query.Lines 5–7: The main query is the SQL statement that uses the CTE. We can reference the CTE as if it were a table or subquery in the main query.
Let’s discuss a simple example of a CTE that calculates the total revenue for each category of products. In this example, we’ll be using the following two tables.
Products
Product_id | Product_name | Price | Category |
1 | Widget A | 19.99 | 1 |
2 | Widget B | 24.99 | 2 |
3 | Widget C | 14.99 | 1 |
4 | Widget D | 29.99 | 2 |
5 | Widget E | 9.99 | 1 |
Categories
Category_id | Category_name |
1 | Electronics |
2 | Clothing |
Now, let’s calculate and display the total revenue by category using a CTE as follows:
-- Calculate total revenue by category using a CTEWITH CategoryTotal AS (SELECTc.category_name,SUM(p.price) AS total_revenueFROM products pJOIN categories c ON p.category = c.category_idGROUP BY c.category_name)SELECTcategory_name,total_revenueFROM CategoryTotalORDER BY total_revenue DESC;
WITH CategoryTotal AS (...): This is the start of the CTE namedCategoryTotal. Inside the CTE, a query joins theproductsandcategoriestables to calculate the total revenue for each category using theSUMfunction and theGROUP BYclause.SELECT ... FROM CategoryTotal: This is the main query that uses the CTE. It selects the category names and total revenues calculated in the CTE and orders the results in descending order based on total revenue.
When we run this SQL script, it will create a database, define two tables, insert sample data into those tables, and then calculate and display the total revenue by category using a CTE.
Benefits of using CTEs
There are several benefits of using CTEs. Some of them are:
Readability: CTEs make complex queries easier to read and understand by breaking them into logical, named components.
Reusability: We can reference the same CTE multiple times in a query, reducing redundancy and making our SQL code more maintainable.
Recursive queries: CTEs are often used for recursive queries, where a query refers to itself to traverse hierarchical data structures.
Window functions: CTEs are frequently used in conjunction with window functions to perform complex analytical tasks.
Free Resources
