The WITH clause, also known as Common Table Expressions (CTEs), is a powerful feature in MySQL that allows for creating temporary named result sets within a query. It enhances query readability, modularity, and performance. Here are some examples of how the WITH clause can be used in complex MySQL queries.

mysql db server

Recursive Queries:

    SELECT 1 AS n
    SELECT n + 1
    FROM cte
    WHERE n < 10

This query uses a recursive CTE to generate a series of numbers from 1 to 10. The recursive part of the CTE references itself until the termination condition (`n < 10`) is met.

Multiple CTEs:

    cte1 AS (
        SELECT column1, column2
        FROM table1
        WHERE condition
    cte2 AS (
        SELECT column3, column4
        FROM table2
        WHERE condition
SELECT cte1.column1, cte2.column3
FROM cte1
JOIN cte2 ON cte1.column2 = cte2.column4;

This query demonstrates the usage of multiple CTEs. The first CTE (`cte1`) retrieves specific columns from `table1` based on a condition, while the second CTE (`cte2`) retrieves columns from `table2`. The final query joins the two CTEs based on a common column.

Aggregation and Filtering:

WITH sales_data AS (
    SELECT customer_id, SUM(order_total) AS total_sales
    FROM orders
    GROUP BY customer_id
SELECT customer_id, total_sales
FROM sales_data
WHERE total_sales > (SELECT AVG(total_sales) FROM sales_data);

This query calculates the total sales for each customer using a CTE (`sales_data`). The final query then filters the result set to only include customers whose total sales are above the average of all customers.

Self-Join Using CTEs:

WITH employees_hierarchy AS (
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
    FROM employees e
    INNER JOIN employees_hierarchy eh ON e.manager_id = eh.employee_id
SELECT employee_id, employee_name, level
FROM employees_hierarchy;

This query builds a hierarchical employee structure using a CTE (`employees_hierarchy`). It recursively retrieves each employee's manager based on the `manager_id` column until there are no more managers. The final query selects the employee ID, name, and their respective level in the hierarchy.

These examples illustrate the versatility and power of the WITH clause (CTEs) in complex MySQL queries. Whether it's recursive queries, multiple CTEs, aggregation and filtering, or self-joins, the WITH clause enhances the readability and flexibility of queries, allowing for more efficient and modular query construction.