question

Bob Hovious avatar image
Bob Hovious asked

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?

ctebest-practicesub-querycommon-table-expression
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Steve Jones - Editor avatar image
Steve Jones - Editor answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jay Bonk avatar image
Jay Bonk answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlexpert avatar image
sqlexpert answered
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][1] > [1]: http://www.youtube.com/watch?v=Ly-YqPVdVOk&feature=youtu.be
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jasonwisdom avatar image
jasonwisdom answered
A contrarian view: I think CTEs are evil. Sure there examples on the internet showing how they are easier to read (which I disagree with BTW...temporary tables, @tables and non-correlated subqueries I can read quite well. This spaghetti code of nested CTEs gets quite complicated). The problem is when you get into 1,000+ line stored procedures and functions. Right now I am debugging a query written by a team of CTE-believers whose cascading recursion of CTEs several nested 11 layers deep (CTE 2 calls CTE1, CTE3 calls CTE2 and CTE1, ..., CTE11 calls everything) are producing over 1,100 tuples in memory causing OOM errors. CTEs are not the sole cause; "WHERE NOT EXISTS", UNION, and correlated subqueries are also contributing. And poorly-written SQL is nothing new. But this code is every bit as complex to read and debug as if things were all in one statement. At least when views were overused, they could be corrected in one place and did not need to be edited in dozens of stored procedures. I just don't see any possible value-add of CTEs that cannot already be accomplished by views and temp tables/table variables. Views are cleaner, and temporary storage is much faster. In theory, simple CTEs make code readability great. In practice, CTEs allow for code to become even more FUBAR than in the past.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hstarkar87 avatar image
hstarkar87 answered
Advantages of using common table expression (CTE) http://www.codesolution.org/?p=529
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.