JIYIK CN >

In this tutorial, we will learn about MySQL WITHCONSTRUCTED TE clauses, also known as Common Table Expression(CTE). CTE is used whenever you want to manipulate data with difficult subqueries.

We will also learn Common Table Expressionhow CTEs (Complex Type Enumerations) allow you to write complex queries in a way that is easy to read and understand. We will also look at whether we can use nested WITHclauses.

Please note that this is not available prior to MySQL version 8.0 Common Table Expression. You must have MySQL 8.0 or higher to use it. You can see what's new in MySQL version 8.0 here.

Use the MySQL WITHclauseCommon Table Expression

To use MySQL WITHclauses, let's first understand CTE. Common table expressions (CTE) are named temporary result sets that exist only within the execution scope of the statement in which it is written.

By using WITHthe clause, you can give a complex subquery a name that you can easily use in the main query ( SELECT, INSERT, UPDATEor DELETE). Keep in mind that not all databases support WITHthe clause.

WITHYou can use one or more subqueries and CTEs in the same IN clause, but not nested WITH( WITHanother within an IN clause WITH). Let's create a CTE called tb_order Tableand fill it with some data to exercise WITHthe IN clause.

Sample 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 your table was created successfully in Tablesunder .Database

Use the following INSERTcommand 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 SELECTthe command to view the data.

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

At this point, we will use WITHthe clause to use common table expressions and operate complex subqueries as shown 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 the above query into its parts to understand it:

Common table expressions:cte_order

Subquery:

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;

Note that the CTE references itself in the main query to read the data. It will display the following output based on my data (your output may be different).

公用表表达式Scope of Implementation

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

When you select only the code highlighted by the green box, you are still cte_orderwithin the execution scope of the CTE named , but when you select only the code within the red box, you are now outside the execution scope and cannot reference the common table expression named . This means that you can reference the CTE in cte_orderthe same clause in which you wrote it .WITH

Using 公共表表达式MySQL with multiple WITHclauses

Let's practice using multiple common table expressions WITH.

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;

You will now see the following output.

Similarly, we can also reference a Common Table Expression defined previously from another CTE. Make sure both common table expressions are written in the same WITHclause.

in conclusion

Considering the above discussion, we conclude that WITHthe clause is used to obtain 公共表表达式advantages that help in manipulating difficult subqueries. We can WITHuse multiple subqueries and common table expressions in the same INSERT clause, but we cannot have nested INSERT WITHclauses. We also cannot reference CTEs from different WITHINSERT clauses.