>>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.

Absolutely not. I have no idea why you are thinking that. Sure its part of the ANSI definition and sure it is implemented in all major RDBMS's. And as I outlined in the other mail SQL server uses the CTE as a inline declaration of the whole statment while Postgress currently really executes the CTE and materializes this at the cost of less optimal optimization. That is the reason why they are changing the default for non-recursive queries to inline declaration rather than materializing it.

Oracle is yet different as it tries to determine the cost of using it inline or materializing it. Not sure whether there are hints possibe. SQL server does not materialize it, but there is an enhancement request for a table hint.

>It is funny you are putting constraints on the use of CTE.

I'm not, just noting that the use of CTE's are overused in situations where subqueries produce a better readable query. Not only for the ones who are versed in SQL, but also the ones who occasionally write some SQL.

>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.

Eh... I'd be very carefull with that statement. I've seen statments with using many nested CTEs causing havoc in maintainability. Its nice you are abstracting away the complexity in the SQL statment but you are also abstracting away the details you need when you need to tweak performance and or debug it. Things get really ugly when you have a larger number of CTEs where its definition is difficult to comprehend its meaning in the real world.

The thing I had to do is go the opposite rout and substitute the CTE with subqueries in order to find the problem and to optimize the plan.

Yes, you can use it in a correct way to abstract away complexity in the final statement, but that really is hiding problems if you don't really know what a particular CTE stands for. A better exercise is make sure to identify the CTEs have that some meaning and create views or inline functions out of it if possible. It will save you a lot o headaches in the future and is much easier to maintain and extend and above is reusable.

So no, I don't share the enthusiasm for CTEs when used to replace subqueries as I think they often are misused or overused where it would be better to use either subqueries or views or inline functions.

>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).

I think you are lost in the concept of materializing CTEs and inline CTEs.

A filter outside of the CTE will never be applied to the CTE if it is materialized. So this is not applicable to Postgress (currently). SQL server and Oracle are able to do this as the CTE is just a definition that gets inserted into the queryplan before optimizing it. So yes a filter outside of the CTE will be "optimizing" (sort of) the CTE because its result does never exist in memory, but is just part of the whole query plan.

>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:


It is different if you got a 200+ line SQL statement where serveral CTEs with fuzzy definitions are at the top and are using it two pages down. The novice reader will look at a statement

SELECT SomefieldsGodknowsWhereTheyWereDefined, ......
      FROM Cte_1
            LEFT JOIN Cte_2 ON .....
            INNER JOIN Cte_3 ON .... (Trying to figure out all where the fields in here are defined)
     WHERE ... (Again trying to figure out where all the fields are coming from and what the expression really means)

They really will have to scroll up and down to look up the fields and grasp the defitions of the CTE in order to make sense of it.
In the case of subqueries you have a much better sense as all the table and fieldnames are guaranteed coming from the database and you can read into it definition without scrolling two pages up and down.

In a 200+ line query CTEs and esspecially nested ones can get really ugly very quickly.

>

>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).

Again, you don't seem to realize that the repitions are refering to type 2 CTEs, of which I'm far more sympathetic than single use CTEs. So I did not constrain them away.

A subquery is always in a place where you need it when you are debugging a problem or optimizing the query as a whole. This is the major problem with a CTE whose definition might be several pages above the actual query you are maintaining

The trick is that you format your query in a way that it becomes readable.

Yes, we agree to disagree