Difference between CTE and SubQuery?

sql sql-server tsql subquery common-table-expression
dance2die picture dance2die · Apr 1, 2009 · Viewed 82k times · Source

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?

Answer

Marc Gravell picture Marc Gravell · Apr 1, 2009

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.

Related questions

Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?

In this excellent SO question, differences between CTE and sub-queries were discussed. I would like to specifically ask: In what circumstance is each of the following more efficient/faster? CTE Sub-Query Temporary Table Table Variable Traditionally, I've used lots of …

Read More
T-SQL Subquery Max(Date) and Joins

I'm trying to join multiple tables, but one of the tables has multiple records for a partid with different dates. I want to get the record with the most recent date. Here are some example tables: Table: MyParts Partid Partnumber …

Read More
SELECT with multiple subqueries to same table

I'm using the same SQL pattern over and over, and I know there has to be a better way, but I'm having trouble piecing it together. Here's a simple version of the pattern, where I'm pulling back the student's information …

Read More