SQL Nested Query Processing

Question

Suppose a Nested query is like :

SELECT * 
FROM Table1 
WHERE Table1.val in 
(
   Select Table2.val 
   from Table2 
   where Table2.val>3
)

So my question is how do actual query processors evaluate this -

Do they first evaluate inner most query, store the result temporarily and then use it in the upper level query? [ If the result of subquery is large, temporary storage may not be enough ]
Or do they evaluate the outer query for each result of the outer query [ requires too many evaluations of outer query]

Am I missing something, and which way is it actually implemented ?

Hmm.. Well a query processor would first convert the query from what you've written into relational operations and then perform optimizations on those operations. These micro-operations will then be combined to give you the result you want. — cjds
– cjds, Commented Oct 2, 2013 at 16:15

Joel Coehoorn · Accepted Answer · 2013-10-02 16:23:10Z

3

It depends. You're allowed to reference a value from the outer query inside the inner. If you do this, you have what is referred to as a correlated sub-query or correlated derived table. In this case, it has to recompute the query for every possible row in the parent query. If you don't this, you have what is referred to as an inline view or inline derived table, and most database engines are smart enough to only compute the view once.

answered Oct 2, 2013 at 16:23

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cjds · Accepted Answer · 2013-10-02 16:22:55Z

This might help you

There is plenty of memory available to run fairly large queries and there is little chance of the query's complexity creating a timeout. Nested queries have the advantage of being run almost exclusively in physical memory for the sake of speed, unless the amount of memory needed to perform the query exceeds the physical memory "footprint" allocated to SQL Server.

Also, because of a nested query's structure, the SQL Server query optimizer will attempt to find the most optimal way to derive the results from the query. (i.e. the optimizer will convert it into micro-ops and reorganize to run it efficiently) [Source]

Due to the re-ordering which query is run first is hard to say. After the optimization most probably however a form of the inner query will be run (you can't really do stuff without that result most of the time).

SQL queries don't run like stack calls the way you seem to think. They're a one line instruction which is comprehended and translated for the machine.

yakiro · Accepted Answer · 2013-10-02 16:33:48Z

0

You can use EXPLAIN to get information about the execution plan like this:

EXPLAIN SELECT * 
 FROM Table1 
 WHERE Table1.val in 
(
  Select Table2.val 
  from Table2 
  where Table2.val>3
)

For example for your query you'll get enter image description here

and as the documentation states:

DEPENDENT SUBQUERY evaluation differs from UNCACHEABLE SUBQUERY evaluation. For DEPENDENT SUBQUERY, the subquery is re-evaluated only once for each set of different values of the variables from its outer context. For UNCACHEABLE SUBQUERY, the subquery is re-evaluated for each row of the outer context.

So your first guess was the correct one.

For more information about EXPLAIN see: http://dev.mysql.com/doc/refman/5.0/en/explain.html

answered Oct 2, 2013 at 16:33

yakiro

7634 silver badges9 bronze badges

Collectives™ on Stack Overflow

SQL Nested Query Processing

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related