Which is better? CTEs (common table expressions) or Subqueries?

Seeder question.

In SQL2005 and beyond, you can use CTEs or you can continue to use subqueries.

What are the pros and cons of each?

more ▼

asked Oct 25, 2009 at 11:37 PM in Default

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

I think the biggest benefit for using CTEs is readability. It makes it much easier to see what queries are being used as subqueries, and then it's easy to join them into a query, much like a view.

CTEs do allow recursion, which can solve some interesting problems, or get you into trouble.

more ▼

answered Oct 25, 2009 at 11:51 PM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

A beautiful answer. Remember that in most cases an expression with a CTE generates exactly the same execution plan.

I wrote a little bit about the use of CTE's for readability at http://www.sqlservercentral.com/articles/Common+Table+Expression+%28CTE%29/62404/ .
Oct 26, 2009 at 02:05 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

The biggest pro for a CTE is that a CTE can be recursive where a subquery cannont.

Also, a CTE can be referenced multiple ties in the same statement, where a subquery cannot.

Personally, I think that in most cases, a CTE in is more readable than a subquery.

more ▼

answered Oct 25, 2009 at 11:53 PM

Jay Bonk gravatar image

Jay Bonk
1.4k 2

(comments are locked)
10|1200 characters needed characters left

The only disadvantage I see with CTEs is that they require the keyword WITH. This forces the previous statement to end with a semi-colon, which causes headaches for some developers.

Otherwise, I see CTEs as a way of using table-expressions in a much more readable way. I wish that all sub-queries could be defined this way, including scalar subqueries, IN lists and EXISTS queries. They should be parameterisable and able to be used in a lambda-style way.

more ▼

answered Oct 26, 2009 at 01:52 AM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

(comments are locked)
10|1200 characters needed characters left

I think the guys here have pretty much nailed it in terms of the key benefit being readability. However, I would just like to point out a common misconception - which is that a CTE is 'run' and then the rest of the query uses it.

It doesn't work like that - a CTE will work as you would think sub-queries would, and will be materialised once for each time it is used in the query.

For single materialisation, a temporary storage mechanism (temp table or table variable) can still offer efficiencies over both methods.

more ▼

answered Oct 26, 2009 at 06:29 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left

CTE is common table expression. This video have complete information about CTE along with its properties and examples for CTE.

Very informative video. Please watch and share your comments.

< http://www.youtube.com/watch?v=Ly-YqPVdVOk&feature=youtu.be >

more ▼

answered Mar 11, 2013 at 01:48 PM

sqlexpert gravatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 25, 2009 at 11:37 PM

Seen: 9495 times

Last Updated: May 27 at 06:57 PM