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