Multiple WITH Statements in SQL: Comprehensive Guide

Ryan Forrester

Sep 19th, 2024

Blog

SQL’s WITH clause, also known as Common Table Expressions (CTEs), is a great for complex queries. But did you know you can use multiple WITH statements in a single query?

Let’s dive into multiple WITH statements and see how they can change your SQL queries.

The Basics: What Are WITH Statements?

Before we jump into multiple WITH statements, let’s quickly recap what a single WITH statement does:

WITH sales_summary AS (
    SELECT 
        product_id,
        SUM(quantity) as total_quantity,
        SUM(price * quantity) as total_revenue
    FROM sales
    GROUP BY product_id
)
SELECT * FROM sales_summary WHERE total_revenue > 10000;

This query creates a temporary named result set, sales_summary, which we can then use in the main query. It’s like creating a virtual table that exists only for the duration of the query.

Stepping It Up: Using Multiple WITH Statements

Now, let’s see how we can use multiple WITH statements to break down complex queries into manageable pieces:

WITH 
monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', sale_date) as month,
        product_id,
        SUM(quantity) as total_quantity,
        SUM(price * quantity) as total_revenue
    FROM sales
    GROUP BY DATE_TRUNC('month', sale_date), product_id
),
top_products AS (
    SELECT 
        month,
        product_id,
        total_revenue,
        RANK() OVER (PARTITION BY month ORDER BY total_revenue DESC) as rank
    FROM monthly_sales
)
SELECT tp.month, tp.product_id, p.product_name, tp.total_revenue
FROM top_products tp
JOIN products p ON tp.product_id = p.id
WHERE tp.rank <= 3
ORDER BY tp.month, tp.total_revenue DESC;

In this example, we’ve used two WITH clauses:

monthly_sales: Calculates monthly sales for each product.
top_products: Ranks products within each month based on revenue.

The main query then uses these CTEs to find the top 3 products for each month.

Real-World Scenario: Analyzing Customer Behavior

Let’s look at a real-world scenario where multiple WITH statements shine. Imagine you’re an e-commerce analyst trying to identify your most valuable customers based on their purchasing habits and demographics.

WITH 
customer_purchases AS (
    SELECT 
        customer_id,
        COUNT(DISTINCT order_id) as total_orders,
        SUM(order_total) as total_spent,
        AVG(order_total) as avg_order_value
    FROM orders
    WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
    GROUP BY customer_id
),
customer_demographics AS (
    SELECT 
        c.id as customer_id,
        c.age,
        c.gender,
        a.city,
        a.country
    FROM customers c
    JOIN addresses a ON c.address_id = a.id
),
customer_segments AS (
    SELECT 
        cp.*,
        cd.age,
        cd.gender,
        cd.city,
        cd.country,
        CASE 
            WHEN cp.total_spent > 1000 AND cp.total_orders > 10 THEN 'High Value'
            WHEN cp.total_spent > 500 OR cp.total_orders > 5 THEN 'Medium Value'
            ELSE 'Low Value'
        END as customer_segment
    FROM customer_purchases cp
    JOIN customer_demographics cd ON cp.customer_id = cd.customer_id
)
SELECT 
    customer_segment,
    COUNT(*) as segment_count,
    AVG(age) as avg_age,
    AVG(total_spent) as avg_total_spent,
    AVG(avg_order_value) as avg_order_value
FROM customer_segments
GROUP BY customer_segment
ORDER BY avg_total_spent DESC;

This query uses three WITH statements to:

Calculate purchase metrics for each customer
Gather demographic information
Segment customers based on their value

The main query then provides a summary of each customer segment.

Optimizing Performance with Multiple WITH Statements

While multiple WITH statements can make your queries more readable, they can also improve performance in certain scenarios:

1. Avoiding Redundant Calculations

By breaking down complex queries into smaller CTEs, you can avoid repeating the same calculations multiple times. This is especially useful when you need to reference the same subquery in multiple places.

2. Improved Query Planning

Database engines can often optimize queries with multiple WITH statements more effectively. By separating complex logic into distinct CTEs, you give the query planner more opportunities to optimize each part independently.

3. Simplifying UNION Operations

When you need to combine results from multiple similar queries, using WITH statements can make UNION operations much cleaner:

WITH 
active_users AS (
    SELECT user_id, 'active' as status
    FROM users
    WHERE last_login > DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
),
inactive_users AS (
    SELECT user_id, 'inactive' as status
    FROM users
    WHERE last_login <= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
)
SELECT * FROM active_users
UNION ALL
SELECT * FROM inactive_users;

This approach is much more readable than nesting subqueries within a UNION statement.

Multiple WITH Statements with a Rapid Database Builder

When working with SQL, executing efficient queries is straightforward, but sometimes complex operations, like using multiple WITH statements, require extra attention.

This is where rapid database builders like Five come into play. Five provides a MySQL database for your application and generates an automatic UI, making it easier to interact with and manage your data.

With Five, you can handle tasks like chaining multiple CTEs in SQL intuitively through its generated forms and tables. This allows you to focus on writing accurate queries without worrying about the complexities of structuring multiple WITH statements manually.

Five also allows you to create interactive forms, tables, dynamic charts, comprehensive PDF reports, and dashboards that are automatically generated based on your database schema. This makes it easier to visualise and analyze data.

In addition, Five lets you write custom JavaScript and TypeScript functions, giving you the flexibility to implement custom business logic.

Once your application is ready, Five makes deployment easy with just a few clicks, allowing you to deploy your MySQL- based application to a secure, scalable cloud infrastructure.

This lets you focus on developing, while Five handles the complexities of cloud deployment and infrastructure management.

If you’re serious about using SQL efficiently, especially when using multiple WITH statements, give Five a try.

Build Your Database In 3 Steps
Start Developing Today

Advanced Techniques: Recursive WITH Statements

One feature of WITH statements is the ability to create recursive queries. This is particularly useful for handling hierarchical or graph-like data structures.

Let’s look at an example where we have an employee table with a self-referencing manager_id column:

WITH RECURSIVE employee_hierarchy AS (
    -- Base case: top-level employees (no manager)
    SELECT id, name, manager_id, 0 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees with managers
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT 
    id,
    name,
    level,
    REPEAT('  ', level) || name as hierarchical_name
FROM employee_hierarchy
ORDER BY level, name;

This query uses a recursive WITH statement to build a hierarchical view of employees, showing their level in the organization and indenting their names accordingly.

Combining Multiple WITH Statements and Recursive Queries

You can even combine multiple WITH statements, including recursive ones, to tackle extremely complex problems. Here’s an example that finds the shortest path between two nodes in a graph:

WITH RECURSIVE
graph_edges AS (
    -- Define the graph edges
    SELECT 'A' as source, 'B' as target, 1 as distance UNION ALL
    SELECT 'B', 'C', 2 UNION ALL
    SELECT 'A', 'C', 3 UNION ALL
    SELECT 'C', 'D', 1 UNION ALL
    SELECT 'B', 'D', 4
),
paths(source, target, path, total_distance) AS (
    -- Base case: direct connections
    SELECT source, target, ARRAY[source, target], distance
    FROM graph_edges

    UNION ALL

    -- Recursive case: extend paths
    SELECT p.source, ge.target, p.path || ge.target, p.total_distance + ge.distance
    FROM paths p
    JOIN graph_edges ge ON p.target = ge.source
    WHERE NOT ge.target = ANY(p.path)  -- Avoid cycles
)
SELECT path, total_distance
FROM paths
WHERE source = 'A' AND target = 'D'
ORDER BY total_distance
LIMIT 1;

This query first defines the graph edges, then uses a recursive CTE to find all possible paths, and finally selects the shortest path from ‘A’ to ‘D’.

Wrapping Up: Multiple WITH Statements

We’ve explored the versatility and efficiency of using multiple WITH statements in SQL. From simplifying complex queries to enabling advanced recursive operations, this technique is a valuable addition to any SQL developer’s toolkit.

Key takeaways:

Multiple WITH statements can break down complex queries into manageable, reusable parts.
They can improve query readability and maintainability.
In many cases, they can enhance query performance by avoiding redundant calculations.
Recursive WITH statements open up new possibilities for handling hierarchical and graph data.
Combining multiple WITH statements, including recursive ones, allows for solving extremely complex problems elegantly.

As you continue to work with SQL, experiment with multiple WITH statements in your own queries. You’ll likely find that they not only make your code cleaner but also open up new ways of thinking about and structuring your database operations.