In which sequence are queries and sub-queries executed by the SQL engine?

by

I think answer 4 is correct. There are a few considerations:

type of subquery – is it corrrelated, or not. Consider: 

SELECT *
FROM   t1
WHERE  id IN (
             SELECT id
             FROM   t2
            )

Here, the subquery is not correlated to the outer query. If the number of values in t2.id is small in comparison to t1.id, it is probably most efficient to first execute the subquery, and keep the result in memory, and then scan t1 or an index on t1.id, matching against the cached values.

But if the query is:

SELECT *
FROM   t1
WHERE  id IN (
             SELECT id
             FROM   t2
             WHERE  t2.type = t1.type
            )

here the subquery is correlated – there is no way to compute the subquery unless t1.type is known. Since the value for t1.type may vary for each row of the outer query, this subquery could be executed once for each row of the outer query.

Then again, the RDBMS may be really smart and realize there are only a few possible values for t2.type. In that case, it may still use the approach used for the uncorrelated subquery if it can guess that the cost of executing the subquery once will be cheaper that doing it for each row.

Leave a Comment

tech