MySQL 8.0 WITH query details

Learning about WITH queries in MySQL 8

1. Example
3. Practice

Learning about WITH queries in MySQL 8

Preface:

For logically complex SQL, with can greatly reduce the number of temporary tables and improve the readability and maintainability of the code.
MySQL 8.0 finally supports the with statement. For complex queries, there is no need to write so many temporary tables.
You can view the official documentation [click to jump]

1. Example

From the first official example, we can see that the query statement creates four temporary tables: cte1 , cte2 , cte3 , and cte4 The latter temporary tables depend on the data of the former temporary tables.
The last line is the final query result. In fact, ct4 contains 3 rows of data because ct3 results, but MAX and MIN are used to get one row of results.

WITH cte1(txt) AS (SELECT "This "),
     cte2(txt) AS (SELECT CONCAT(cte1.txt,"is a ") FROM cte1),
     cte3(txt) AS (SELECT "nice query" UNION
                   SELECT "query that rocks" UNION
                   SELECT "query"),
     cte4(txt) AS (SELECT concat(cte2.txt, cte3.txt) FROM cte2, cte3)
SELECT MAX(txt), MIN(txt) FROM cte4;
 
+----------------------------+----------------------+
| MAX(txt) | MIN(txt) |
+----------------------------+----------------------+
| This is a query that rocks | This is a nice query |
+----------------------------+----------------------+
1 row in set (0,00 sec)

The second official example is the use of recursion. Based on reading the documentation, I analyzed the query results below.
First define a temporary table my_cte Analyze SELECT 1 AS n , this determines the temporary table column name is n, the value is 1
Then SELECT 1+n FROM my_cte WHERE n<10 , this is a recursive query n<10 , and 1+n is used as the result to fill the temporary table. Finally, SELECT * FROM my_cte is used to query the temporary table, so the query result is obvious.

WITH RECURSIVE my_cte AS
(
  SELECT 1 AS n
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<10
)
SELECT * FROM my_cte;
 
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0,00 sec)

According to my understanding, I wrote the following two different queries, and the query results are the same.
It is worth noting that the number and type of multiple query columns in the temporary table must be the same, otherwise an error will be reported.

This specifies the temporary table column name in the first row WITH RECURSIVE my_cte(a,b,c) AS
(
  SELECT 1,1,1
  UNION ALL
  SELECT 1+a,2+b,3+c FROM my_cte WHERE a<10
)
SELECT * FROM my_cte;
 
The first row does not specify column names, and the column names are determined by the results returned by the first query WITH RECURSIVE my_cte AS
(
  SELECT 1 AS a,1 AS b,1 AS c
  UNION ALL
  SELECT 1+a,2+b,3+c FROM my_cte WHERE a<10
)
SELECT * FROM my_cte;

According to the official documentation, the syntax template for a temporary table is as follows, which can be composed of queries with many rows.

WITH RECURSIVE cte_name [list of column names ] AS
(
  SELECT ... <-- specifies initial set
  UNION ALL
  SELECT ... <-- specifies initial set
  UNION ALL
  ...
  SELECT ... <-- specifies how to derive new rows
  UNION ALL
  SELECT ... <-- specifies how to derive new rows
  ...
)
[, any number of other CTE definitions ]

The official document also lists that when using temporary tables, you can add, delete, modify, and query new tables. You can read the official document for details.

3. Practice

Recursive exercises are mainly used for tables containing parent node IDs, etc. For details, please refer to the exercises below.
Define the following table to store the id, name and pid of each region (province, city, district)

 
CREATE TABLE tb(id VARCHAR(3), pid VARCHAR(3), name VARCHAR(64));
 
INSERT INTO tb VALUES('002', 0, 'Zhejiang Province');
INSERT INTO tb VALUES('001', 0, 'Guangdong Province');
INSERT INTO tb VALUES('003', '002', 'Quzhou City');
INSERT INTO tb VALUES('004', '002', 'Hangzhou');
INSERT INTO tb VALUES('005', '002', 'Huzhou City');
INSERT INTO tb VALUES('006', '002', 'Jiaxing City');
INSERT INTO tb VALUES('007', '002', 'Ningbo City');
INSERT INTO tb VALUES('008', '002', 'Shaoxing City');
INSERT INTO tb VALUES('009', '002', 'Taizhou City');
INSERT INTO tb VALUES('010', '002', 'Wenzhou City');
INSERT INTO tb VALUES('011', '002', 'Lishui City');
INSERT INTO tb VALUES('012', '002', 'Jinhua City');
INSERT INTO tb VALUES('013', '002', 'Zhoushan City');
INSERT INTO tb VALUES('014', '004', 'Uptown');
INSERT INTO tb VALUES('015', '004', 'Downtown');
INSERT INTO tb VALUES('016', '004', 'Gongshu District');
INSERT INTO tb VALUES('017', '004', 'Yuhang District');
INSERT INTO tb VALUES('018', '011', 'Jindong District');
INSERT INTO tb VALUES('019', '001', 'Guangzhou');
INSERT INTO tb VALUES('020', '001', 'Shenzhen City');
 
WITH RECURSIVE cte AS (
 SELECT id,name FROM tb WHERE id='002'
 UNION ALL
 SELECT k.id, CONCAT(c.name,'->',k.name) AS name FROM tb k INNER JOIN cte c ON c.id = k.pid
) SELECT * FROM cte;

Execution Result:

The analysis result includes the data of the first row SELECT id,name FROM tb WHERE id='002' . At this time, there is only one row of data in the table. Then the table is joined and SELECT k.id, CONCAT(c.name,'->',k.name) AS name FROM tb k INNER JOIN cte c ON c.id = k.pid is queried. The parent node data is recursively put into the temporary table and the final query is the recursive result.

This is the end of this article about MySQL WITH query details. For more relevant MySQL WITH query content, please search 123WORDPRESS.COM’s previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: