Detailed explanation of the usage example of WITH in MYSQL

WITH clause (also known asPublic table expressionsCommon Table Expression, CTE for short) is a powerful query building tool in SQL that can significantlyImprove readability and maintainability of complex queries。

1. Basic syntax structure

WITH cte_name AS (
    SELECT ...  -- definitionCTEQuery
)
SELECT ... FROM cte_name;  -- Main query usageCTE

2. The core characteristics of CTE

• Temporary result set: CTE only exists during the current query execution
• Quoteability: After definition, it can be referenced multiple times in the main query
• Scope limits: Only valid in a single statement immediately following it

3. Specific usage of CTE in MySQL

1. Basic CTE (single-table expression)

WITH sales_summary AS (
    SELECT product_id, SUM(quantity) AS total_sold
    FROM orders
    GROUP BY product_id
)
SELECT p.product_name, s.total_sold
FROM products p
JOIN sales_summary s ON p.product_id = s.product_id;

2. Multiple CTE definitions (comma-separated)

WITH 
customer_orders AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
),
high_value_customers AS (
    SELECT customer_id
    FROM customer_orders
    WHERE order_count > 5
)
SELECT c.customer_name
FROM customers c
JOIN high_value_customers h ON c.customer_id = h.customer_id;

3. Recursive CTE (supported by MySQL 8.0+)

Recursive CTE is used to process hierarchical data:

WITH RECURSIVE org_hierarchy AS (
    -- Basic Query（Anchor members）
    SELECT id, name, parent_id, 1 AS level
    FROM organization
    WHERE parent_id IS NULL
    UNION ALL
    -- Recursive query（Recursive member）
    SELECT , , o.parent_id,  + 1
    FROM organization o
    JOIN org_hierarchy h ON o.parent_id = 
)
SELECT * FROM org_hierarchy;

4. Advantages of CTE

Improve readability：

• Decompose complex queries into logical blocks
• Similar to variable definitions in programming

Avoid duplicate subqueries：

-- Not usedCTE（Repeat subquery）
SELECT * FROM (SELECT ... FROM table1) AS t1
JOIN (SELECT ... FROM table1) AS t2...
-- useCTE（Avoid duplication）
WITH t1 AS (SELECT ... FROM table1)
SELECT * FROM t1 JOIN t1 AS t2...

Support recursive query: Process tree/hierarchical data

5. The difference between CTE and temporary tables

6. Practical application scenarios

1. Complex report query

WITH 
monthly_sales AS (...),
product_ranking AS (...)
SELECT ... FROM monthly_sales JOIN product_ranking...

2. Data cleaning pipeline

WITH 
raw_data AS (...),
cleaned_data AS (...),
enriched_data AS (...)
SELECT * FROM enriched_data;

3. Hierarchical structure traversal (organization structure, comment thread, etc.)

WITH RECURSIVE comment_tree AS (...)
SELECT * FROM comment_tree;

7. Performance precautions

Materialization tips：

WITH cte_name AS (
    SELECT /*+ MATERIALIZE */ ...  -- Forced materialization
)

Merge Tips：

WITH cte_name AS (
    SELECT /*+ MERGE */ ...  -- Force merge to the main query
)

Recursive depth control(MySQL default 1000):

SET @@cte_max_recursion_depth = 2000;

8. Version compatibility

• MySQL 8.0+ fully supports CTE and recursive CTE
• MySQL 5.7 and earlier does not support CTE

WITH clauses are an indispensable tool in modern SQL development. Rational use can greatly improve the clarity and maintenance of queries, especially when dealing with multi-layer nested or recursive data.

This is the end of this article about the detailed explanation of the usage examples of WITH in MYSQL. For more related content on mysql with usage, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!