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

Question

Hello I made a SQL test and dubious/curious about one question:

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

the answers was

1. primary query -> sub query -> sub sub query and so on
2. sub sub query -> sub query -> prime query
3. the whole query is interpreted at one time
4. There is no fixed sequence of interpretation, the query parser takes a decision on fly

I choosed the last answer (just supposing that it is most reliable w.r.t. others). Now the curiosity:

where can i read about this and briefly what is the mechanism under all of that?

Thank you.

Answer 1

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.