What is the syntax of with as mysql

1. WITH as syntax

WITH as syntax is mainly divided into the following two parts:

1) WITH : Keyword, used to introduce one or more temporary tables.

2) AS: keyword, used to define one or more temporary tables.

The general syntax of the WITH as statement is as follows:

WITH [RECURSIVE] cte_name (column_name1,column_name2,...) AS
 (
   subquery1
   UNION [ALL]
   subquery2
   UNION [ALL]
   subquery3
   ...
 )
SELECT *
FROM cte_name;

Among them,

cte_name: the name of the temporary table;

column_name1,column_name2,...: Column names in the temporary table;

subquery1, subquery2, subquery3...: subquery statements.

2. WITH as practical application

The practical application scenarios of WITH as statement mainly include the following aspects:

1) Recursive query

By using the WITH statement, you can implement recursive query and obtain a hierarchical result set. The following takes an employee table as an example to illustrate:

WITH RECURSIVE emp AS (
   SELECT emp_id, emp_name, emp_manager_id
   FROM employees
   WHERE emp_manager_id IS NULL 
   UNION ALL
   SELECT e2.emp_id, e2.emp_name, e2.emp_manager_id
   FROM employees e2
   INNER JOIN emp ON e2.emp_manager_id = emp.emp_id
)
SELECT * FROM emp;

In the above statement, the basic part of the recursive query is to look up the root node in the table (that is, employees without managers). In each recursive iteration, the table The subquery finds the subordinates of each node in one go and adds them to the result set.

2) Organize the result set

Using the WITH statement, multiple query results can be combined together to perform multi-table join queries. The following takes a product sales and inventory table as an example to illustrate:

WITH sales AS (
   SELECT p.product_id, p.product_name, s.sale_id, s.sale_date, s.quantity, s.price
   FROM products p
   INNER JOIN sales s ON p.product_id = s.product_id
), inventory AS (
   SELECT p.product_id, p.product_name, i.inventory_id, i.quantity
   FROM products p
   INNER JOIN inventory i ON p.product_id = i.product_id
)
SELECT s.sale_id, s.sale_date, s.product_id, s.product_name, s.quantity, s.price, i.inventory_id, i.quantity as inventory_quantity
FROM sales s
INNER JOIN inventory i ON s.product_id = i.product_id;

In the above statement, we first define two temporary tables, sales and inventory, and then perform a joint query on these two tables to output the results. set. This result set contains sales information from the sales table and inventory information from the inventory table.

3) Subquery optimization

WITH as statement can also be used to optimize subqueries. Subquery statements are often inefficient and can lead to confusing code and errors. The WITH as statement can improve the readability and maintainability of queries. The following takes an order and order details table as an example to illustrate:

WITH ord AS (
   SELECT order_id
   FROM orders
   WHERE order_date > '2022-01-01'
)
SELECT *
FROM order_details
WHERE order_id IN (SELECT order_id FROM ord);

We define a temporary table named "ord" to store order information that meets the conditions. Then in subsequent queries, directly use the WHERE order_id IN (SELECT order_id FROM ord) statement to query the order details. This not only improves query efficiency, but also improves code readability and maintainability.

The above is the detailed content of What is the syntax of with as mysql. For more information, please follow other related articles on the PHP Chinese website!