How To Use Multiple CTEs In SQL

SQL language provides a wide variety of functions, clauses, JOINs, etc. that help to provide additional features to solve complex problems. One more thing that SQL language has is the CTE. CTE stands for ‘Common Table Expression’. It’s a momentary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement in SQL. CTEs are particularly valid for breaking down complicated queries into more manageable, modular components.

Generally, in normal SQL queries, we use single CTEs for problem-solving. But sometimes the problems are more complicated and need logical solutions. In this situation, we can use multiple CTEs in a single query. This article is based on the technique of using multiple CTEs in one query.

You can refer to the following article for more details about CTEs:

MySQL Common Table Expression (CTE)

Advantages of Using Multiple CTEs in SQL

• Modularity: You can convert complicated queries into small pieces of fragmented sections, making them shorter as they are, therefore, simple.
• Reuse: CTEs are the workaround for queries that can be named multiple times within a single query to allow access to the code to be reused with this same logic.
• Clarity: Instruction on naming the distinctive elements of your queries helps in the improvement of the clarity status, and understandability of your SQL code.
• Optimization: Based on the specification of the database engine, using CTEs can be beneficial in some cases, where the optimizer gains knowledge about query structure, resulting in better performance.

Using Multiple CTEs In SQL

To implement two CTEs in a single query we need two sample tables. Let’s consider sampletable1 and sampletable2 with different columns.

CREATE TABLE sampletable1 (
    column1 INT,
    column2 VARCHAR(50)
);
INSERT INTO sampletable1 (column1, column2)
VALUES
    (1, 's1'),
    (2, 's2'),
    (3, 's3');

CREATE TABLE sampletable2 (
    column3 INT,
    column4 VARCHAR(50)
);

INSERT INTO sampletable2 (column3, column4)
VALUES
    (1, 'N1'),
    (3, 'N2'),
    (5, 'N3');
SELECT *
FROM sampletable1;
SELECT *
FROM sampletable2;

Code language: SQL (Structured Query Language) (sql)

Below is a query that will set data from sampletable1 where column1 is less than 3 and data from table2 where column3 is less than 4, then it will join the results of these two CTEs based on the equality condition CTE1.column1 = CTE2.column3.

WITH 
    CTE1 AS (
        SELECT column1, column2
        FROM sampletable1
        WHERE column1 < 3
    ),
    CTE2 AS (
        SELECT column3, column4
        FROM sampletable2
        WHERE column3 < 4
    )
SELECT *
FROM CTE1
JOIN CTE2 ON CTE1.column1 = CTE2.column3;
Code language: SQL (Structured Query Language) (sql)

This example is a sample. In this way, you can use two or more CTEs and combine the results. Let’s see an outcome for verification.

Real-World Example of Multiple CTEs in SQL

In this example, let’s implement two CTEs to calculate the profit in the business model. This is a real-world application of multiple CTEs in one query. The logic is simple. First, we will create two tables: Ordersample and expensesample. The first CTE will calculate the total sale for each customer ID. The second CTE will help to calculate the total expense for each customer ID. Then we will combine both in the main query to calculate the profit for each customer.

CREATE TABLE Orderssample (
    OrderID INT,
    CustomerID INT,
    OrderTotal DECIMAL(10, 2)
);


INSERT INTO Orderssample (OrderID, CustomerID, OrderTotal)
VALUES
    (1, 101, 100.50),
    (2, 102, 75.25),
    (3, 103, 150.75),
    (4, 101, 200.00),
    (5, 102, 50.50);


CREATE TABLE Expensessample (
    ExpenseID INT,
    CustomerID INT,
    ExpenseAmount DECIMAL(10, 2)
);

INSERT INTO Expensessample (ExpenseID, CustomerID, ExpenseAmount)
VALUES
    (1, 101, 50.00),
    (2, 102, 25.00),
    (3, 103, 75.00),
    (4, 101, 30.00),
    (5, 102, 20.00);
    

WITH SalesCTE AS (
    SELECT
        CustomerID,
        SUM(OrderTotal) AS TotalSales
    FROM
        Orderssample
    GROUP BY
        CustomerID
),
ExpensesCTE AS (
    SELECT
        CustomerID,
        SUM(ExpenseAmount) AS TotalExpenses
    FROM
        Expensessample
    GROUP BY
        CustomerID
)
SELECT
    s.CustomerID,
    s.TotalSales,
    e.TotalExpenses,
    s.TotalSales - e.TotalExpenses AS Profit
FROM
    SalesCTE s
JOIN
    ExpensesCTE e ON s.CustomerID = e.CustomerID;
Code language: SQL (Structured Query Language) (sql)

In the results, we can clearly see the profit is calculated for each customer ID.

Use Cases of Multiple CTEs in SQL

Multiple Common Table Expressions (CTEs) are in SQL and they are applied very flexibly in different situations to make the query easy to read, get uncluttered and perform better. Here are some common use cases for using multiple CTEs in SQL:

1. Complex Data Transformation

There are situations when you have to execute multiple steps of data conversion before query planning. In this case, multiple CTEs can be used to divide the process into smaller, manageable parts. Every CTE might be a transformation step that has a clear meaning. The ease of orientation over the whole logic becomes evident at that point.

2. Recursive Queries

If examples, where you handle recursive operations, are present, such as when you operate hierarchical data traversal (like organizational charts or product categories), you can use many CTEs to handle different parts of the recursion. Also, an example is: that the first CTE generates the primary nodes while the second CTE recalls the nodes in a recursive process.

3. Subquery Replacement

Therefore, instead of using nested subqueries, one can use multiple CTEs to put logic in their independent sections to make both the code readable and sustainable. Each CTE is a subquery that can be reused within the main query turning it into a single elegant solution.

4. Data Aggregation and Filtering

The big data that you are trying to analyze might require limitations and consolidations of data thus demanding more steps for data aggregation and filtering. The reason for using many CTEs is that they facilitate the procedure of operations in a set-wise manner and combine the results at the end fairly quickly.

Summary

In this article, we have learned the technique and advantages of using multiple CTEs in SQL language. The multiple CTEs help to solve complex problems with ease. The sample example is implemented with the two CTEs in a single query. Hope you understand the concept and enjoy the article.

Reference

https://stackoverflow.com/questions/2140750/keeping-it-simple-and-how-to-do-multiple-cte-in-a-query