>I was referring to situations where I use a CTE to solve hierarchical problems through recursion.
>
>You outlined that you can use recursive CTE's to generate rows, which indeed was something I overlooked in my initial reply and should have stated that in the three types of CTE's
>1. Recursive (which previously I referred to as solving hierarchical problems)
>2, To use a CTE multiple times in a Query
>3. As a subquery
>Did I forget one ??
>
>As I said,
>For type 1, you absolutely want to use CTE's.
>For type 2, you might want to use them to cut code and reduce potential errors. However I would say that if the CTE is something is a univerally known entity (like invoice totals, order history) you might want to consider to use views or inline functions as you can easily re-use them for other queries and maintain them in one single place. CTEs are often used where views or inline functions are a better fit.
>For Type 3 where a CTE is only used once as a subquery, I stay with my opinion they are overused. It seldomly makes a query more readable and it most cases it results in a longer statement.
>
>As for your example below, I wonder why you would throw me a Postgress SQL statement with json in it. I'm not doing postgress and I don't know the exact datatypes and syntax of its json handling, so your message gets lost here. why is this an example of something you cannot do or less efficient in a subquery?
>
>And perhaps, I would even agree this is an applicable case to use it because of seperating the generation of values and the actual query (a big difference with using CTEs as substitution of subqueries). But this is not the typical case of CTEs we were discussing here. We were discussing the overuse of a CTE in type 3 queries like the one you initially wrote in at the beginning of this thread.
>

That is where your idea is basically lacking. You think CTEs are constrained to MS SQL Server and I said before it is in ANSI SQL, not specific to MS SQL Server, and actually there are deviations between implementations.

It is funny you are putting constraints on the use of CTE. That query I throw at you might be postgreSQL but that doesn't matter, everyone dealing with SQL can read that much postgreSQL code, what you should notice in that query was two queries back to back one using another. That certainly makes that much more readable than it is "subquery" counterpart. Just consider, there are more complex queries that might include many more 'subqueries' as a CTE, that one is utilizing the ones before that. That certainly makes it more readable.

There is also another aspect of a CTE, when you refer the content of a CTE query in a filter, outside of it, based on the implementation that filter might be applied directly to the CTE itself. I am not sure if you could say the same for a subquery (at least CTE one is documented as a feature, haven't heard the same for a subquery).

You can't type the CTEs we are discussing here. What you made the topic of this discussion is just based on readability of a query, writing the same thing in two different ways. And most of the time, the generated query plan is the same, it would be all about syntax. To you, a subquery is more readable, to me a CTE. How do we measure it?

select * from customers where customerId = 'BONAP'

from c in customers where customerId == 'BONAP' select c

If you consider, both are doing the same thing, which one is more readable? For someone who spent his life writing SQL queries would say first one. OTOH for someone else, perhaps never dealt with SQL, second is more readable and logical. Second one also resembles the actual order of execution. It is not surprising it was chosen as the syntax of Linq so the query writing could benefit from intellisense.

A CTE case is not much different:

with X as (...) select ... from x

select .. from ( .... ) x

we are reading from top to bottom, left to right. With the CTE version we know what x is when we come to select. In case of subquery, go read the subquery first and then come back to top to read main. And if you think you could have many subqueries as X, Y, Z, .... and some might be using data from others like:

with X as (....),
Y as (...),
Z as (select ... from X inner join Y on ... where ...)
...

it can quickly become unreadable with a "subquery" version - let alone the unavoidable repetitions (but you constrained them away, I don't know why).