What are CTEs?
CTE is also known as the WITH clause or Sub Query factoring. It is a named temporary result set that is used to manipulate the complex sub-queries data
In simple terms, the code can be made reusable if the already calculated values are stored in a temporary space eliminating the need to calculate them again.
It can be used with SELECT, INSERT, UPDATE, DELETE, etc. Also, a CTE can reference other CTEs that are part of the same WITH clause, but those CTEs should be defined earlier.
MySQL CTE syntax
The structure of CTE includes :
- name - Name of the CTE to store the temporary result
- column list - Column to store the values in the CTE
- query to define CTE - the result of this query is stored in the CTE
CTE is always defined with the
WITH keyword, followed by the CTE name, and column name. Once the CTE has been declared, it can be referenced to make use of the results. The execution scope of CTE exists within the Statement in which it is used.
WITH cte_name (column_list) AS ( query ) SELECT * FROM cte_name;
The number of columns in the CTE arguments must be equal to the number of columns in the query. The query columns that define the CTE will be used if we haven't declared the columns in the CTE parameters.
MySQL CTE example
Let us assume ABC company sells goods to its customers online. Here is the
orders table representing their weekly sales.
We have the order and the orderDetails table with order having basic info of order and orderDetails having price breakdown of the order. We want to get the order with its totalOrderValue. To write a query for this, we need to calculate the totalOrderValue and then join the two tables.
- Create a CTE -
- Store average in the column
- Join CTE with the
orderstable and show the orders along with
WITH `orderValue` (`orderNumber`, `orderTotalValue`) AS ( SELECT `orderNumber`, SUM( `orderdetails`.`priceEach`*`orderdetails`.`quantityOrdered` ) AS `orderTotalValue` FROM `orderdetails` GROUP BY `orderNumber` ) SELECT `orders`.`orderNumber`, `orderTotalValue`, `orderDate`, `status` FROM orders INNER JOIN `orderValue` ON `orderValue`.`orderNumber`=`orders`.`orderNumber`;
Entries of those customers who ordered more than the average order price.
MySQL CTE use case
XYZ company has multiple electronics stores selling laptops, phones, desktops, etc. online. Here is the
sales table representing the weekly sales of all of the stores under XYZ.
We are supposed to show the entries of those stores having sales higher than the average sales wrt all the stores.
- Calculate the total sales of each store
- Calculate the average sales wrt all the stores
- Show entries of all the stores whose total sales are greater than the average sales
Without using MySQL CTE
Find total sales of each store
SELECT s.store_id, sum(s.cost) AS total_sales_per_store FROM sales s GROUP BY s.store_id;
Total sales of each store -
Find average sales with respect to all stores
SELECT (avg(total_sales_per_store)) avg_sale_for_all_store FROM (SELECT s.store_id, sum(s.cost) AS total_sales_per_store FROM sales s GROUP BY s.store_id) x
Average sales of each store
Display data of all stores whose sales were better than the average sales across all stores
SELECT * -- Total sales FROM (SELECT s.store_id, sum(s.cost) as total_sales_per_store FROM sales s GROUP BY s.store_id ) total_sales -- avg sales JOIN (SELECT avg(total_sales_per_store) avg_sale_for_all_store FROM (SELECT s.store_id, sum(s.cost) AS total_sales_per_store FROM sales s GROUP BY s.store_id) x ) avg_sales -- condition ON total_sales.total_sales_per_store > avg_sales.avg_sale_for_all_store;
Using MySQL CTE
As it gets difficult to read this code, we can Common Table Expressions to store the results of already calculated parameters. The total sales have been calculated twice which can be avoided.
We can use the CTEs to temporarily store the total sales in the
total_sales CTE and create
Similarly, average sales in the
avg_sales CTE and
After creating CTE, we can join
total_sales and display all the entries that satisfy the condition of
WITH total_sales AS (SELECT s.store_id, sum(s.cost) AS total_sales_per_store FROM sales s GROUP BY s.store_id), -- Total sales avg_sales as (SELECT (avg(total_sales_per_store)) avg_sale_for_all_store FROM total_sales) -- average sales SELECT * FROM total_sales JOIN avg_sales ON total_sales.total_sales_per_store > avg_sales.avg_sale_for_all_store;
MySQL Recursive CTE Syntax
When a subquery in a common table expression (CTE) refers to the CTE name itself, the CTE is said to be recursive.
The recursive CTEs are used for series generation and traversal of hierarchical or tree-structured data.
The recursive CTE syntax consists of 3 elements :
- Initial query (anchor) - Base result set of the CTE structure. It is a non-recursive statement.
Recursive query - Query that references the CTE name, hence, it is called a recursive member. The recursive member is joined with the anchor member by a
- Termination condition - ensures the recursion stops when the recursive member returns no row.
WITH RECURSIVE ctename AS ( initial_query -- anchor UNION ALL recursive_query -- recursive member that references the CTE name ) SELECT * FROM ctename;
Steps to execute a recursive CTE
- The anchor/Initial query is executed at first to store the base case’s result. This base result is used for the next iterations.
- The recursive query is then executed until the termination condition.
- The anchor member and the result from the recursive query is combined using the
MySQL Recursive CTE example
Consider a case to generate 10 order id’s starting from 1. Look at it as a while loop which keeps on executing until the termination condition.
WITH RECURSIVE natural_sequence AS ( SELECT 1 AS order_id -- anchor member: our sequence starts from 1 UNION ALL SELECT order_id + 1 FROM natural_sequence -- recursive member: reference to itself WHERE order_id < 10 -- stop condition ) SELECT * FROM natural_sequence; -- executes CTE
The non-recursive part gets executed
SELECT 1 AS order_id
It produces initial rows which consist of 1 column named
order_idand a single row with entry 1.
The recursive part gets executed. It will add rows until
order_idbecomes 10 the condition becomes false and the recursion is terminated and a series of 10 order ids are generated.
MySQL recursive CTE use cases
Case 1 - Series Generation
Consider ABC shop selling stationery items online. Here is the
salestable representing the weekly sales of ABC. We are supposed to show the total sales for each day according to the dates. Lets first show total sales per day using
SELECT order_date,SUM(price) AS sales FROM sales GROUP BY order_date;
Output: The manager of ABC wants to have a look at the sales of the first week of February i.e. from 1st - 7th of February. Also whichever day has 0 sales, 0.00 should be allocated to that day. This kind of weekly sales data can be used to create charts/graphs to understand the sales of the shop. We can use recursive CTE here to generate dates from 1st Aug to 7th Aug and later merge them with the
salestable. We are using the
COALESCEoperator to allocate
SUMof prices to days having sales and 0 to the ones having no sales. Read more about
WITH RECURSIVE dates(date) AS ( SELECT '2020-02-01' -- anchor UNION ALL SELECT date + INTERVAL 1 DAY -- recursive query FROM dates WHERE date < '2020-02-07' ) --termination condition SELECT dates.date, COALESCE(SUM(price), 0) sales FROM dates LEFT JOIN sales ON dates.date = sales.order_date GROUP BY dates.date;
Output: Representing the weekly sales of the ABC shop
Case 2 - Hierarchical Data Traversal
Recursive CTEs can be used to play around with hierarchal data like the understanding hierarchy of employees in a company or a family tree, etc. Consider Amazon LLC which has multiple products like Amazon Prime, Amazon Market Place, Amazon Alexa, Amazon Echo Dot, etc. These products have sub-products like Amazon Echo Dot comes under Alexa or Amazon Fresh and comes under the Amazon Marketplace. Below is the organizational structure of amazon The
amazontable represents the same where level indicating under which category the category falls. We are supposed to show which category falls under which organization. As Amazon fresh comes under the marketplace, we are supposed to show ‘Amazon > Amazon Market place > Amazon Fresh to show the hierarchy. Let's create a recursive CTE
WITH RECURSIVE hierarchy(id, name, path, level) AS ( SELECT id, company_name, CAST(company_name AS CHAR(100)), 1 FROM amazon WHERE level IS NULL UNION ALL SELECT am.id, am.company_name, CONCAT(h.path,' -> ',am.company_name), h.level+1 FROM hierarchy h JOIN amazon am ON h.id=am.level ) SELECT * FROM hierarchy ORDER BY level;
Output: Representing the hierarchy of organization in Amazon LLC.
Limitations of Recursive CTE
Aggregate methods like
DISTINCT, and Window functions shouldn't be used by the recursive CTE. For CTE that is not recursive, this is not true. The CTE must only be mentioned once in the recursive
FROMclause and never in a subquery.
- By utilizing temporary tables and the WITH clause, MySQL CTE can be used to prevent the need for complex queries.
- It can also be used to chain CTE in order to make the query simpler.
- The CTE allows us to use it in place of the VIEW idea. It can also be used to quickly build recursive queries.
- Recursive CTEs can be used to create series and navigate through data that is organized in a tree or hierarchy.