>>Thank you very much for the detailed explanation. And for the links to your articles. Your descriptions really helps to understand. I already printed one of your articles on Handling Hierarchical Data.
>>As to CTE, I don't know if this will work on my PC since I still use SQL Server 2008 Developers Edition. I may try a simple example just to make sure the CTE works on this version of SQL Server and I can test a different approach.
>
>Dmitry, you already received some good answers, just a few additional items....
>
>At the simplest level (and forgetting about hierarchical queries for a moment), a CTE is nothing more than syntax sugar. It's a way to take a derived table subquery (or more than 1) and place it "above" the main SELECT, for readability.
>
>The following two lines of code do the same thing. (I realize this is so simple an example that you'd never actually write something this way, but just to illustrate the point) . Let's say you wanted to get invoices for 2023 that had a type of "internal".
>
>
>;with tempcte as (select * from Invoices where year(InvoiceDate) = 2023)
>select * from tempcte where invoicetype = 'Internal'
>
>
>versus
>
>select * from ( select * from invoices where year(InvoiceDate) = 2023) t where InvoiceType = 'Internal'
>
>
>Again, in reality, you'd write it as one straight query.....but just to illustrate the point, in the first query, all SQL Server is doing is allowing you to place the "main" query up in a CTE, and reference it. Under the hood, SQL Server is treating it as one query......it's not a case where there's a "temp table" in between the "with" line and the "select line"......it's basically a syntax trick so that you can place the syntax above. Some developers like to use this for readability when writing more complex queries against derived tables.
>
>If you're not querying hierarchical data, I can't think of a reason why a CTE would be REQUIRED.....(I can certainly understand how people prefer them for readability). But if you're not doing anything hierarchical, there's nothing a CTE can do that in-line, derived table subqueries can't do.
>
>It's when you get to hierarchical data that CTEs and recursive queries really shine. For a few years I worked on a cost model project where we had a cost buildup based on steel going through so many different chemical and facility processes....and the list was variable. So being able to recursively query to get cumulative numbers was a major help.

Good points. I think the original question from this thread is a good example of CTEs increasing readability and clarity. When you have nested subqueries, it can be hard to follow the logic.

One other case where a CTE is clearly favored is when multiple queries within the structure are based on the results of the same subquery. That is, when you might otherwise have to include the same subquery more than once if you don't use a CTE.

Tamar