T-SQL 101: 123 Using Multiple CTEs in a Single T-SQL Query

I mentioned earlier that CTEs can be used to clean up or tidy more complicated queries. You will find people though, who really don’t like them at all. My aim here isn’t to argue for or against them, as it’s a complex topic, but to make sure you know how to write queries with them, and more importantly, to be able to read queries that use them. At this point, I want you to understand the syntax.

I also said that they only exist for the duration of the query. A single query though, can contain multiple CTEs. In the skeleton code above, I’ve defined a row set called BulkProducts, then one called GoodCustomers, and yet another called OverdueCustomers. Each of these can then be used in the main query below.

This can allow us to take complex concepts and break them into chunks, one at a time and then write a SELECT or whatever at the bottom that’s then far simpler than what it would have been if all of these derived tables were embedded all through the code.

Referring to CTEs from within CTEs

Another thing that may not be immediately obvious is that in one of the later table expressions, I can refer to an earlier one.

So when I could use the row set BulkProducts while I’m defining GoodCustomers, or when I’m defining OverdueCustomers or both.

In SQL Server, you can use an earlier CTE within the next layer of the CTE or in the main query.

Forward References?

Even though these all look like separate sets of rows of data, SQL Server T-SQL does not allow forward references. A CTE can only reference previous CTEs. When I’m defining BulkProducts, I cannot refer to OverdueCustomers.

Note that other database engines like PostgreSQL do allow this type of reference, when you use the RECURSIVE keyword. (It’s an odd choice as it’s not necessarily recursive).

Recursive CTEs

What SQL Server does allow, for certain specific CTEs is recursive CTEs. BulkProducts in the example above could refer to itself.

These types of queries need to be designed very carefully and are often better replaced with temporary table-based queries. They also have very strict rules about how they are designed, and T-SQL is even stricter than most SQL dialects about this.

We’ll talk about how these work in T-SQL later.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-03-02