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.

More Related Contents:

  1. Join vs. sub-query
  2. What is the difference between LATERAL JOIN and a subquery in PostgreSQL?
  3. SQL Joins Vs SQL Subqueries (Performance)?
  4. Difference between CTE and SubQuery?
  5. updating table rows in postgres using subquery
  6. Does SparkSQL support subquery?
  7. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  8. subquery in FROM must have an alias
  9. Return pre-UPDATE column values using SQL only
  10. How to delete from select in MySQL?
  11. How can I insert values into a table, using a subquery with more than one result?
  12. Difference between Subquery and Correlated Subquery
  13. What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
  14. Nested select statement in SQL Server
  15. Postgres Error: More than one row returned by a subquery used as an expression
  16. Oracle: Combine multiple results in a subquery into a single comma-separated value [duplicate]
  17. SQL Oracle LEFT JOIN and SUBQUERY error: ORA-00905: missing keyword
  18. How do I perform update query with subquery in Access?
  19. Procedurally transform subquery into join
  20. How to combine date from one field with time from another field – MS SQL Server
  21. STRING_SPLIT in SQL Server 2012
  22. count(*) vs count(column-name) – which is more correct? [duplicate]
  23. Left Outer Join using + sign in Oracle 11g
  24. Sequence vs identity
  25. What is the difference between Views and Materialized Views in Oracle?
  26. Execution sequence of Group By, Having and Where clause in SQL Server?
  27. Conditional SQL count
  28. SELECT query with CASE condition and SUM()
  29. Product() aggregate function
  30. How to insert Arabic characters into SQL database?

Leave a Comment