MySQL With Clause

1. Use of MySQL WITH Clause a.k.a. Common Table Expression
2. Use of MySQL WITH Clause Having Multiple Common Table Expressions
3. Conclusion

In this tutorial, we will learn about MySQL WITH clause, also known as Common Table Expression (CTE). CTEs are used whenever you want to manipulate difficult sub-queries data.

We will also learn how Common Table Expression (CTE) allows you to write complex queries in an easily readable and understandable way. We will also see whether we can use the nested WITH clause or not.

Please note that Common Table Expression was not available before MySQL Version 8.0. You have to have MySQL Version 8.0 or above to use it. You can see what is new in MySQL Version 8.0 here.

Use of MySQL WITH Clause a.k.a. Common Table Expression

To use MySQL WITH clause, let’s understand CTEs first. Common Table Expressions (CTEs) are named temporary result set that only exists in execution scope within that statement in which it is written.

By using the WITH clause, you can assign a name to a complex sub-query that you can easily use within the main query (SELECT, INSERT, UPDATE, or DELETE). Keep in mind that all databases do not support the WITH clause.

You can use one or multiple sub-queries, and CTEs within the same WITH clause, but you can’t use Nested WITH (another WITH inside the WITH clause). Let’s create a Table named tb_order and populate it with some data to practice the WITH clause.

Example Code:

# SQL Programming Using MySQL Version 8.27
CREATE TABLE `practice_with_clause`.`tb_order` (
ORDER_ID INTEGER NOT NULL,
CUSTOMER_FIRST_NAME	VARCHAR(30) NOT NULL,
CUSTOMER_LAST_NAME VARCHAR(30) NOT NULL,
CITY_NAME VARCHAR(64) NOT NULL,
PURCHASED_PRODUCTS VARCHAR(64) NOT NULL,
ORDER_DATE DATE NOT NULL,
PRIMARY KEY (ORDER_ID)
);

Make sure in your Database under Tables that your table is successfully created.

Use the following INSERT command to populate the table with 7 records.

# SQL Programming Using MySQL Version 8.27
INSERT INTO practice_with_clause.tb_order 
(ORDER_ID, CUSTOMER_FIRST_NAME, CUSTOMER_LAST_NAME, CITY_NAME, PURCHASED_PRODUCTS, ORDER_DATE)
VALUES
(1,'John','Horton', 'Washington', 'Books', '2021-05-03'),
(2,'Banji','Horton',  'Florida', 'Pens', '2010-5-6'),
(3,'Nayya','Sofia',  'South Carolina', 'Books', '2011-10-15'),
(4,'Martell','Daniel',  'Michigan', 'NoteBooks', '2012-12-02'),
(5,'Sana','Preston',  'Michigan', 'White Board Marker', '2013-08-27'),
(6,'Gulraiz','Yonja', 'Washington', 'Books', '2021-05-03'),
(7,'Mashal','Naaz',  'Florida', 'Comic Books', '2019-01-01');

Now, use the SELECT command to view data.

# SQL Programming Using MySQL Version 8.27
SELECT * FROM practice_with_clause.tb_order;

At this point, we’ll use the WITH clause to use Common Table Expression and manipulate complex sub-query as given below.

# SQL Programming Using MySQL Version 8.27
WITH cte_order AS 
(
SELECT PURCHASED_PRODUCTS, COUNT(ORDER_ID) as Number_of_Orders
FROM practice_with_clause.tb_order
GROUP BY PURCHASED_PRODUCTS
)
SELECT AVG(Number_of_Orders) AS "Average Orders Per Category"
FROM cte_order;

Let’s break down the above query into sections to understand:

Common Table Expression: cte_order

Sub-query:

SELECT PURCHASED_PRODUCTS, COUNT(ORDER_ID) as Number_of_Orders FROM practice_with_clause.tb_order GROUP BY PURCHASED_PRODUCTS

Main-query:

SELECT AVG(Number_of_Orders) AS "Average Orders Per Category" FROM cte_order;

Notice that the CTE is referencing itself in the main query to read the data. It will show the following output according to my data (your output may be different).

Common Table Expression Execution Scope

As we said that CTE only works within its execution scope, how? See the following screenshot.

When you only select the code highlighted with the green box, you remain within the execution scope of CTE named cte_order, but when you only select the code within the red box, you are out of execution scope now and can’t reference the Common Table Expression named cte_order. It means you can reference the CTE within the same WITH clause in which it is written.

Use of MySQL WITH Clause Having Multiple Common Table Expressions

Let’s practice the WITH clause by using multiple Common Table Expressions.

WITH 
cte_order AS 
(
SELECT PURCHASED_PRODUCTS, COUNT(ORDER_ID) as Number_of_Orders
FROM practice_with_clause.tb_order
GROUP BY PURCHASED_PRODUCTS
),
cte_location AS
(
SELECT COUNT(CITY_NAME) as City
FROM practice_with_clause.tb_order
WHERE CITY_NAME = 'Washington'
)
SELECT AVG(Number_of_Orders) AS "Average Orders Per Category", City
FROM cte_order,cte_location;

Now, you will see the following output.

Similarly, we can also refer to one Common Table Expression previously defined from another CTE. Make sure that both Common Table Expressions are written within the same WITH clause.

Conclusion

Considering the above discussion, we have concluded that the WITH clause is used to get the advantage of Common Table Expression that helps manipulate the difficult sub-queries. We can use multiple sub-queries and Common Table Expressions within the same WITH clause but can’t have a nested WITH clause. We also can not refer to the CTEs from different WITH clauses.