CODEWITHSUNDEEP
t-sql
Martijn
Martijn

Reputation: 24789

What is the execution order of a query with sub queries?

Consider this query

select * 
from documents d
where exists (select 1 as [1]
              from (
                    select *
                    from ( 
                        select * 
                        from ProductMediaDocuments 
                        where d.id = MediaDocuments_Id
                        ) as [dummy1] 
                    ) as [s2]
                    where exists( 
                                select *
                                from ProductSkus psk
                                where psk.Product_Id = s2.MediaProducts_Id
                                )
              )

Could someone tell me how this is being processed by SQL Server? When statements appears in parentheses, this means it will execute first. But does this also apply for the above statement? In this case I don't think so, because the sub queries needs values of outer queries. So, how does this works under the hood?

Upvotes: 0

Views: 106

Answers (4)

Hogan
Hogan

Reputation: 70523

I think this code is clearer don't you??

SELECT d.* 
FROM documents d
JOIN ProductMediaDocuments s2 ON d.id = MediaDocuments_Id
JOIN ProductSkus psk ON psk.Product_Id = s2.MediaProducts_Id

Upvotes: 0

dario
dario

Reputation: 5269

That's completely up to the database engine.

Since SQL is a declarative language, you specify WHAT you want, but the HOW part is up to the DB Engine and it really depends on many factors like indexes presence, type, fragmentation; row cardinality, statistics.

That's just to mention few, because the list can goes on.

Of course you can look to the execution plan but the point is that you can't know HOW it will be executed just reading the query.

Upvotes: 2

Stephan
Stephan

Reputation: 6018

I simplified your code a bit.

SELECT * 
FROM documents d
WHERE EXISTS (  SELECT 1
                FROM ProductMediaDocuments s2
                WHERE d.id = MediaDocuments_Id
                AND EXISTS  ( 
                                SELECT *
                                FROM ProductSkus psk
                                WHERE psk.Product_Id = s2.MediaProducts_Id
                            )
              )

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32170

The execution plan will tell you what the engine actually does. That is, the physical processing order. AFAIK, the query planner will rewrite your query if it finds a better way to express it to itself or the engine. If your question is, "Why is my query not working the way I think it should." then that is where you should start.

The doc says the logical processing order is:

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

It also has this note:

The [preceding] steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

FROM would include inline views (subqueries) or CTE aliases. Each time it finds a subquery, it should start over from the beginning and evaluate that query.

Upvotes: 0

Related Questions