question

naveen avatar image
naveen asked

Sub queries limit

How many sub queries can be nested in a query?

t-sqlsub-query
10 |1200

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
10 |1200

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

Blackhawk-17 avatar image
Blackhawk-17 answered

I would propose the same answer as Matt for completeness but would also quote this section fron the link:

A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query.

That way individuals get their answer here, in one click, and have the option of researching further if desired.

10 |1200

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

Håkan Winther avatar image
Håkan Winther answered

Even if you can have up to 32 levels of nested subqueries, I would advice you to not use 32 levels of subqueries.

  • The code will be hard to debug if you run into any problem.
  • SQL will probably have difficulties in optimization etc

As a rule, I use temporary tables rather than using more than 3 levels of subqueries. Ofcourse there are no rules without exceptions, I always test my solutions to find the most optimized query.

10 |1200

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

Jesse McLain avatar image
Jesse McLain answered

Interesting coincidence - 32 is also the max limit on nested stored procedure calls. I discovered that when I wrote some code to determine the natural unique key in a raw set of data (http://jessesql.blogspot.com/2009/02/combination-of-columns-with-unique.html)

10 |1200

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.