x

Sub queries limit

How many sub queries can be nested in a query?

more ▼

asked Oct 26, 2009 at 09:09 AM in Default

naveen gravatar image

naveen
21 1 1 1

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

4 answers: sort oldest
more ▼

answered Oct 26, 2009 at 09:17 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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

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.

more ▼

answered Oct 26, 2009 at 10:18 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

You're more generous than I am. I figured that the homework question probably deserved at least reading the page! :)
Oct 26, 2009 at 10:41 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 26, 2009 at 10:48 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

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

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)

more ▼

answered Oct 26, 2009 at 12:38 PM

Jesse McLain gravatar image

Jesse McLain
106

(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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x987
x46

asked: Oct 26, 2009 at 09:09 AM

Seen: 4391 times

Last Updated: Oct 26, 2009 at 11:45 PM