How can I have multiple common table expressions in a single SELECT statement?

sql

ctes

sql-queries

performance

byAlex Kataev·Dec 4, 2024

In SQL, you can create several common table expressions (CTEs) within a single query. Do this by separating each CTE with commas. Consider complex subqueries like free-standing tables in your main SQL query. Here's a quick example:

WITH
CTE1 AS ( -- First we gather all the employees, like rounding up the Avengers
    SELECT * FROM Employees
),
CTE2 AS ( -- Next, let's bring in the departments, sort of like Avengers HQ
    SELECT * FROM Departments
)
SELECT * FROM CTE1 JOIN CTE2 ON CTE1.DeptID = CTE2.ID; -- Finally, let's let the departments know who's working for them

Remember, each CTE is declared only once, but its power can be tapped multiple times in the main query. Arm yourself with this potent SQL tool for organized, readable, and far more elegant data retrieval.

Building and Utilizing Multiple CTEs

In multiple CTEs, the sequence matters as much as the Infinity Stones in Thanos's gauntlet. If CTE2 leans on CTE1, then CTE1 must be declared first, like so:

WITH
CTE1 AS (
    SELECT * FROM Employees WHERE Status = 'Active'  -- Pick only the active Avengers/employees
),
CTE2 AS (
    SELECT E.ManagerID, COUNT(*) AS NumOfSubordinates -- Time to count the sidekicks!
    FROM CTE1 E
    GROUP BY E.ManagerID
)
SELECT * FROM CTE2;  -- Let's see how many sidekicks each superhero has!

Think of it as a lineup: a preceding CTE can be called upon by those in the line behind, but not the other way around.

Troubleshooting CTE Queries

If SQL were a superhero, it would be Dr. Strange, wielding an arsenal of syntax rules. A common error involves placing commas incorrectly when declaring multiple CTEs. Keep the comma after each CTE declaration (except for the last one) and only put the WITH clause once at the query’s beginning. Consider each CTE an individual mission contributing to a greater cause.

If SQL returns an error like 'Incorrect syntax near 'WITH'', it often suggests a WITH clause used wrongly. Check the order and delimiters in your CTE statement.

Semicolons can be handy too, but remember, they're like punctuation marks and should close your entire CTE statement, not individual CTEs.

Improving Readability and Performance

CTEs are like a well-organized Batcave - tidy, efficient, and useable. Consistent indentation, naming conventions, meaningful comments, and sharp SQL code formatting transform this Batcave into a more welcoming place.

While juggling multiple CTEs, ensure that you're crafting a beautifully readable, maintainable, and 'saves the day' type SQL query.

Like our buddy Flash, SQL Server views each CTE as a non-persistent table. Calling a CTE multiple times might cause the subquery to speed along each time (not always in a good way). If performance outrivals readability, consider creating temporary tables or table variables.

Explore Recursive CTEs

CTEs aren't only about better readability and classy code. They can also dive into recursive queries. How about an SQL query to map an organization's entire hierarchical structure? Here's how CTEs assume the 'recursive' mantle:

WITH RecursiveCTE AS (
    SELECT ID, ParentID, Name, 0 AS Level  -- Root level, like Nick Fury managing all superheroes
    FROM Category
    WHERE ParentID IS NULL  -- The one parent to rule them all

    UNION ALL

    SELECT c.ID, c.ParentID, c.Name, p.Level + 1  -- We’re going deeper now, like a secret superhero base
    FROM Category c
    INNER JOIN RecursiveCTE p ON c.ParentID = p.ID
)
SELECT * FROM RecursiveCTE ORDER BY Level, Name;  -- Let's reveal the hero hierarchy!