Difference between CTE and SubQuery?

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 a CTE 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 over sub-select is that I can actually name the sub-query. Are there any other differences between those two when a CTE is used as a simple (non-recursive) CTE?

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.

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.

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.

One difference that hasn't been mentioned is a single CTE can be referenced in the several parts of a union

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

One important fact that nobody has mentioned is that (at least in postgres), CTEs are optimization fences:

https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/

That is, they will be treated as their own atomic query, rather than folded into the whole query plan. I lack the expertise to give a better explanation, but you should check the semantics for the version of sql you are using; for advanced users, being able to create an optimization fence can help performance if you are expert level in controlling query planner; in 99% of cases, however, you should avoid trying to tell the query planner what to do, because what you think will be faster is likely worse than what it thinks will be faster. :-)

Adding to others' answers, if you have one and the same subquery used several times, you can replace all these subqueries with one CTE. This allows you to reuse your code better.

One thing that you need to understand also is that in older versions of SQL Server (yes many people still need to support SQL Server 2000 databases), CTEs are not allowed and then the derived table is your best solution.

HINT: (MAXRECURSION n)

> you can limit the number of recursion levels allowed for a specific > statement by using the MAXRECURSION hint and a value between 0 and > 32,767 in the OPTION clause

For example, you could try:

OPTION 
      (MAXRECURSION 150)

GO

1. With a CTE, you can use recursion.

2. With a CTE, you only need to write it once, but you can reference it in multiple places within the query. It can therefore allow you to avoid repeating yourself, and might also make the query easier to read and interpret (even in cases where the query only references it once).

3. A CTE appears to provide metadata about itself to the query optimiser, such that if a CTE is referenced more than once in the same query (for example, if it joins to itself), the query optimiser could potentially use that metadata to improve the overall query execution plan (this does not appear to occur with subqueries).

So, in summary, if you want to use recursion, or you think it would make your code more presentable and easier to interpret, or you're using the same subquery more than once, use a CTE.