Difference between CTE and SubQuery?

Question

From this post How to use ROW_NUMBER in the following procedure?

There are two versions of answers where one uses a SubQuery 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?

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

Answer 2

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

Answer 3

The main advantage of the Common Table Expression is twofold. The first is that the result set will be evaluated once and then multiple uses of that resultset in queries that follow will not cause the entire CTE to be reevaluated.

Think of a CTE as a temp table variable that you insert the rows into before the query that uses it (and only the query that uses it).

With a sub-query, if you need to use the results more than once, you have to duplicate the query, but SQL Server might evaluate that twice as well (the optimizer might optimize it out, but that's an implementation detail).

CTEs also have the benefit of being able to be called recursively, but that's not really the important point in the context of this question.

Answer 4

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 100). Useful for calendars, dummy rowsets etc.

They are also more readable (in my opinion).

Apart from this, CTE's and subqueries are identical.

Answer 5

One important difference (not an advantage) is that a CTE is only going to work with SQL Server. If you ever need to move to a different database, it will be a lot more work to refactor the CTE than an equivalent sub query.

On the other hand, once you come to terms with the syntax CTE's are really nice, even if you don't use them recursively, and the odds of moving to a whole new database without having to do a pretty serious re-write are pretty small anyway.

Answer 6

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.