Examples of WITH in complex mysql queries

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.

Recursive Queries:

sql
WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM cte
    WHERE n < 10
)
SELECT * FROM cte;

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:

sql
WITH
    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:

sql
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:

sql
WITH employees_hierarchy AS (
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    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.