SQL Practice Online

How to Use 2 CTEs in a Single SQL Query

By Cristian G. Guasch • Updated: 03/03/24 • 10 min read

Diving into the world of SQL can feel like navigating a labyrinth, especially when you’re trying to streamline your queries for efficiency. One powerful feature that often goes underutilized is the Common Table Expression, or CTE. It’s a game-changer for organizing complex queries, but what if I told you that you could use not just one, but two CTEs in a single SQL query? Yes, it’s entirely possible, and I’m here to guide you through it.

Chat with SQL Databases using AI

AskYourDatabase allows you to chat with your SQL & NoSQL databases to gain insights, visualize data, design table schemas, and data anlysis. Compatible with MySQL, PostgreSQL, MongoDB, and SQL Server.

Start Free Trial

Using two CTEs in a single query can significantly simplify your SQL scripts, making them more readable and easier to maintain. Whether you’re a seasoned SQL pro or just getting your feet wet, understanding how to effectively use multiple CTEs will elevate your data manipulation skills to the next level. Let’s dive into how you can harness the power of multiple CTEs to make your SQL queries more efficient and intuitive.

What is a Common Table Expression (CTE)?

A Common Table Expression, or CTE, is a powerful tool in the SQL toolkit that allows for more modular and readable queries. Unlike subqueries, which can clutter your SQL scripts, a CTE provides a way to write temporary result sets that can be easily referenced within a SELECT, INSERT, UPDATE, or DELETE statement. I find CTEs incredibly useful for breaking down complex queries into simpler, more digestible parts. This makes both writing and reading SQL scripts far more intuitive.

Using two CTEs in a single SQL query isn’t just a practice for SQL wizards; it’s an approachable strategy that I encourage SQL users of all levels to adopt. The technique can greatly enhance the readability of your SQL scripts and make maintenance a breeze. To demonstrate, let’s dive into how to effectively use two CTEs in one SQL statement.

Example with Two CTEs

Consider a scenario where we need to aggregate data from two separate tables before performing a final merge. This is a perfect use case for employing two CTEs.

WITH FirstCTE AS (
SELECT EmployeeID, SUM(Salary) AS TotalSalary
FROM Salaries
GROUP BY EmployeeID
),
SecondCTE AS (
SELECT EmployeeID, COUNT(Distinct ProjectID) AS ProjectsCompleted
FROM Projects
GROUP BY EmployeeID
)
SELECT f.EmployeeID, f.TotalSalary, s.ProjectsCompleted
FROM FirstCTE f
JOIN SecondCTE s ON f.EmployeeID = s.EmployeeID;
  • Forgetting the comma between CTEs: It’s crucial to remember that when defining multiple CTEs, they must be separated by a comma.
  • Incorrectly referencing columns: Ensure that the columns you reference in your final SELECT statement match the ones defined in your CTEs.
  • Variations in Joins: Depending on the relationship between the data in your CTEs, you might use different types of joins (INNER, LEFT, RIGHT, FULL) to merge them correctly.

By using these strategies and being mindful of common pitfalls, you’re well on your way to mastering the art of utilizing two CTEs in a single SQL query. This approach will not only streamline your scripting process but also significantly improve the readability and maintainability of your SQL projects.

Benefits of Using CTEs in SQL Queries

When I first delved into using Common Table Expressions, or CTEs, in SQL, the immediate impact on my scripts was undeniable. CTEs bolster SQL query modularity, making them an invaluable tool in my SQL toolkit. Here, I’ll share the insights and advantages that come with integrating CTEs, especially when utilizing two in a single query.

One significant benefit is the enhancement of query readability. CTEs allow for the decomposition of complex queries into simpler, more digestible parts. This is particularly useful when working on extensive databases where clarity can often be lost amongst the intricacies of SQL scripting. Another advantage is improved query debugging and maintenance. By isolating sections of the query, I can easily pinpoint errors and optimize performance without sifting through cumbersome monolithic statements.

Example of Using Two CTEs in a Single Query

To demonstrate, consider a scenario where I need to aggregate data from two separate tables. Here’s a simple illustration:

WITH FirstCTE AS (
SELECT EmployeeID, COUNT(*) As TaskCount
FROM Tasks
WHERE CompletionDate IS NOT NULL
GROUP BY EmployeeID
),
SecondCTE AS (
SELECT EmployeeID, AVG(Salary) As AvgSalary
FROM Salaries
GROUP BY EmployeeID
)
SELECT f.EmployeeID, f.TaskCount, s.AvgSalary
FROM FirstCTE f
JOIN SecondCTE s ON f.EmployeeID = s.EmployeeID;

This approach simplifies data aggregation by cleanly segmenting the query into manageable parts, each responsible for a specific subset of the data.

Common Mistakes and Variations

However, while using CTEs, it’s essential to be vigilant of certain pitfalls. The most common mistake I’ve encountered is improper comma separation between multiple CTEs, which can easily break the query. Another area to watch is column referencing, ensuring that the columns used in the final SELECT statement accurately reflect those defined in the CTEs.

CTEs offer a range of variations, from simple data aggregation to more complex hierarchical and recursive queries. Adapting the use of two CTEs in a single query isn’t just about technical correctness; it’s about embracing the flexibility and power of SQL to write more readable, maintainable, and efficient code.

Chat with SQL Databases using AI

AskYourDatabase allows you to chat with your SQL & NoSQL databases to gain insights, visualize data, design table schemas, and data anlysis. Compatible with MySQL, PostgreSQL, MongoDB, and SQL Server.

Start Free Trial

Syntax for Using Multiple CTEs in a Single Query

When diving into the syntax for using multiple CTEs in a single SQL query, it’s essential to grasp the basic structure and understand how to properly chain them together. A CTE, or Common Table Expression, acts as a temporary result set which can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. The beauty of using multiple CTEs lies in their ability to organize complex queries into a more manageable form.

The fundamental syntax starts with the WITH clause, followed by the CTE name, an optional column list, and the AS keyword with the CTE’s query enclosed in parentheses. To incorporate multiple CTEs, you simply separate them with a comma after the closing parenthesis of the previous CTE. Here’s the basic scaffolding:

WITH FirstCTE (Column1, Column2) AS (
SELECT Column1, Column2
FROM Table1
),
SecondCTE (Column3, Column4) AS (
SELECT Column3, Column4
FROM Table2
)
SELECT *
FROM FirstCTE
JOIN SecondCTE ON FirstCTE.Column1 = SecondCTE.Column3;

Examples

Let’s look at some variations to understand the flexibility of using multiple CTEs:

  • Aggregating data from different tables:
WITH Sales2020 AS (
SELECT ProductID, SUM(Quantity) AS TotalSales
FROM Sales
WHERE Year = 2020
GROUP BY ProductID
), Sales2021 AS (
SELECT ProductID, SUM(Quantity) AS TotalSales
FROM Sales
WHERE Year = 2021
GROUP BY ProductID
)
SELECT S2020.ProductID, S2020.TotalSales, S2021.TotalSales
FROM Sales2020 S2020
JOIN Sales2021 S2021 ON S2020.ProductID = S2021.ProductID;
  • Using a CTE within another CTE:
WITH FirstLevel AS (
SELECT ID, Name
FROM Employees
WHERE ManagerID IS NULL
), SecondLevel AS (
SELECT E.Name
FROM Employees E
JOIN FirstLevel F ON E.ManagerID = F.ID
)
SELECT *
FROM SecondLevel;

Example Scenario: Implementing Two CTEs in a SQL Query

When working with SQL, using multiple Common Table Expressions (CTEs) within a single query can significantly streamline complex data processing tasks. Let’s dive into a practical example that showcases how to effectively use two CTEs in one query.

Imagine we’re handling sales data and need to calculate both the total sales per country and the average sales per order for each country. This scenario is perfect for employing two CTEs to make our query cleaner and more manageable.

First, we define our initial CTE to calculate total sales per country:

WITH TotalSales AS (
SELECT Country, SUM(Amount) AS TotalAmount
FROM Sales
GROUP BY Country
)

Next, we introduce our second CTE to calculate the average sales per order:

, AverageSales AS (
SELECT Country, AVG(Amount) AS AvgAmount
FROM Sales
GROUP BY Country
)

Let’s join these CTEs together in our final query to achieve our desired results:

SELECT
ts.Country,
ts.TotalAmount,
as.AvgAmount
FROM TotalSales ts
JOIN AverageSales as ON ts.Country = as.Country;

Common Mistakes to Avoid:

  • Forgetting the comma between CTEs: Always remember that when declaring multiple CTEs, each one after the first must be preceded by a comma.
  • Incorrect reference in the final SELECT statement: Be mindful of how CTEs are referenced. Using the wrong alias or CTE name will result in errors.
  • Adding more complex conditions within each CTE to filter the data further.
  • Using CTEs within subqueries to perform even more detailed analyses on the data.

By breaking down our query into two separate CTEs, we organize our logic in a way that’s both easier to understand and maintain. This approach not only makes our SQL queries more efficient but also enhances their readability. Remember, the power of CTEs lies in their versatility and the simplicity they bring to managing complex data structures.

Best Practices for Optimizing Query Performance with Multiple CTEs

When dealing with SQL and particularly when incorporating multiple Common Table Expressions (CTEs) in a single query, it’s crucial to consider how these can affect performance. From my experience, the way you structure and utilize your CTEs can make a significant difference. I’ll share some best practices that have helped me optimize query performance dramatically.

Firstly, always limit the data you bring into a CTE. This might seem obvious, but it’s easy to overlook. Only select the columns you need for subsequent queries. For example:

WITH Sales_CTE AS (
SELECT Country, SUM(Sales) AS TotalSales
FROM Orders
WHERE OrderDate BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY Country
),
AvgSales_CTE AS (
SELECT AVG(TotalSales) AS AvgSales
FROM Sales_CTE
)
SELECT *
FROM AvgSales_CTE;

In the example above, the Sales_CTE is specifically tailored to gather only the necessary data for computing average sales in the AvgSales_CTE. This deliberate limitation ensures efficiency.

Another critical practice is indexing. If your CTE references tables with large datasets, ensure those tables are properly indexed. This might not directly relate to the CTE syntax but has a profound impact on execution speed. Imagine if the Orders table in our example wasn’t indexed on OrderDate; the performance lag would be noticeable.

Common mistakes I’ve seen include:

  • Overusing CTEs for operations that could be simpler with a JOIN or subquery. CTEs are powerful, but they’re not always the best tool for the job.
  • Not naming columns explicitly in the CTE, leading to confusion, especially when dealing with multiple CTEs.

Lastly, consider whether your CTE needs to be materialized. SQL Server, for instance, has an option to materialize the result set of a CTE, effectively treating it like a temporary table. This can be advantageous, particularly when the CTE is referenced multiple times in the main query. Note, the syntax doesn’t change, but understanding when to use this feature requires familiarity with your database’s optimizer hints.

Conclusion

Mastering the use of multiple CTEs in a single SQL query can significantly boost your database queries’ efficiency and performance. By focusing on limiting data selection and indexing, you’re already on the path to optimizing your SQL queries. Remember, the goal isn’t just to make your queries work but to make them work efficiently. Embracing the techniques of materializing CTEs and understanding database optimizer hints can elevate your SQL game. It’s all about striking the right balance and avoiding the pitfalls of overuse. With these strategies in your toolkit, you’re well-equipped to handle complex data structures and make your database work smarter, not harder.

Chat with SQL Databases using AI

AskYourDatabase allows you to chat with your SQL & NoSQL databases to gain insights, visualize data, design table schemas, and data anlysis. Compatible with MySQL, PostgreSQL, MongoDB, and SQL Server.

Start Free Trial

Related articles