Difference between CTE and SubQuery?
Solution 1
In the sub-query vs simple (non-recursive) CTE versions, they are probably very similar. You would have to use the profiler and actual execution plan to spot any differences, and that would be specific to your setup (so we can't tell you the answer in full).
In general; A CTE can be used recursively; a sub-query cannot. This makes them especially well suited to tree structures.
Solution 2
The main advantage of the Common Table Expression (when not using it for recursive queries) is encapsulation, instead of having to declare the sub-query in every place you wish to use it, you are able to define it once, but have multiple references to it.
However, this does not mean that it is executed only once (as per previous iterations of this very answer, thank you to all those that have commented). The query definitely has the potential to be executed multiple times if referenced multiple times; the query optimizer ultimately makes the decision as to how the CTE should be interpreted.
Solution 3
CTE
's are most useful for recursion:
WITH hier(cnt) AS (
SELECT 1
UNION ALL
SELECT cnt + 1
FROM hier
WHERE cnt < @n
)
SELECT cnt
FROM hier
will return @n
rows (up to 101
). Useful for calendars, dummy rowsets etc.
They are also more readable (in my opinion).
Apart from this, CTE
's and subqueries
are identical.
Solution 4
One difference that hasn't been mentioned is a single CTE can be referenced in the several parts of a union
Solution 5
Unless I'm missing something, you can name CTE's and subqueries just as easily.
I guess the main difference is readability (I find the CTE more readable because it defines your subquery up front rather than in the middle).
And if you need to do anything with recursion, you are going to have a bit of trouble doing that with a subquery ;)
Related videos on Youtube
![dance2die](https://i.stack.imgur.com/dWVIl.png?s=256&g=1)
dance2die
I like to read, and build(& break?) stuff. Currently helping folks out on r/reactjs & DEV#react. Reach out to me @dance2die & check out my blog on sung.codes
Updated on October 22, 2021Comments
-
dance2die over 2 years
From this post How to use ROW_NUMBER in the following procedure?
There are two versions of answers where one uses a
sub-query
and the other uses aCTE
to solve the same problem.Now then, what is the advantage of using a
CTE (Common Table Expression)
over a 'sub-query`(thus, more readable what the query is actually doing)The only advantage of using a
CTE
oversub-select
is that I can actually name thesub-query
. Are there any other differences between those two when a CTE is used as a simple (non-recursive) CTE?-
user over 9 yearsDerivative question with good discussion: stackoverflow.com/q/11169550/781695
-
zxq9 over 8 yearsIMO, anyone who thinks a CTE is less readable that a gigantic blob of interwoven subqueries hasn't seen the garbage pile of confusing saw-teeth-shaped queries in use across the majority of enterprise data management systems. Large, non-trivial queries are typically dramatically easier to read later or by new eyes than subqueries, and at least in the case of Postgres magically perform much better in many cases. ([For reasons I have yet to understand[(stackoverflow.com/questions/33731068/…), as the opposite seems more likely.)
-
-
dance2die about 15 yearsSorry, I should have been more clearer in the my question. What would be the difference between CTE and Subquery in the context where CTE is used LIKE subquery?
-
dance2die about 15 years"Think of a CTE as a temp table variable" does that mean CTE is stored in disk or in memory?
-
Xavier Young about 15 yearsYou cannot use the CTE or subquery in multiple queries, by definition. I'm pretty sure that the optimizer handles the subquery the same way it would handle the CTE (evaluating the result set only once, regardless of how many times it is used within the 1 query)
-
casperOne about 15 years@AlexCuse: I think I've clarified the context of the CTE enough, but I added more to try and clarify more.
-
casperOne about 15 years@AlexCuse: There is also no implication that the CTE or subquery can be used in multiple places. The difference between the CTE and optimizer though is that the behavior of the CTE is guaranteed, whereas the behavior of the optimizer is not.
-
casperOne about 15 years@Marc Gravell: We can do more than that though, as the behavior of the profiler is not guaranteed, vs the behavior of the CTE, which is (in terms of evaluation).
-
Xavier Young about 15 yearsand I will concede that there could be some edge cases where the optimizer chokes and the subquery is evaluated more than once, I have not run into any though. Then again, I use CTE's wherever I can ;)
-
casperOne about 15 years@AlexCuse: By multiple places, I mean outside of the query that the subquery or the CTE is attached to. In other words, the CTE is only applicable for ONE query.
-
Xavier Young over 14 yearsI'm not sure there is any non-aesthetic difference (though I expect that in certain situations there may be slight differences in execution plan). Care to enlighten me?
-
kmote over 8 yearsYou can name CTEs, but you can only alias subqueries. The difference is, you can reuse CTEs with multiple aliases (cf. @Michael Petito's example in his comment to casperOne). I don't know of any way to do that with subqueries.
-
Aniket Thakur over 6 yearsNot sure how much this statement make sense for people looking at CTS and subquery difference -
A CTE can be used recursively; a sub-query cannot
. An example would have been great. -
Obinna Nnenanya over 5 yearsIn MSSQL, you need to add a semicolon (;) before WITH, order wise you will get an error. it should be
;WITH blabla AS ...)
-
Quassnoi over 5 years@ObinnaNnenanya: only if it's not the first statement in the batch. Terminating your statements with semicolons is a good idea anyway, even though SQL Server does not enforce it in the current versions other than before
WITH
,MERGE
and similar