CODEWITHSUNDEEP
sqlsql-server
Phillip Senn
Phillip Senn

Reputation: 47635

Does the ON condition limit the amount of work that a joined select does?

I use Microsoft SQL Server, but I suppose this question would be for all SQL Languages. If I had:

SELECT OrderHeaderID
    ,isNull(Qty,0) AS Qty
FROM OrderHeader
LEFT JOIN (
    SELECT OrderHeaderID
    ,Sum(Qty) AS Qty
    FROM OrderDetail
    GROUP BY OrderHeaderID
) OrderDetail
ON OrderDetail.OrderHeaderID = OrderHeader.OrderHeaderID
WHERE Criteria = 'X'

Then will the GROUP BY by be performed on all OrderDetail rows before the criteria is applied? In other words, should I take care to apply the criteria to the GROUP BY as well as to the outer SELECT?

BTW, if you see any improvements to this line of code, then please comment as well.

Upvotes: 3

Views: 63

Answers (3)

alzaimar
alzaimar

Reputation: 4622

Regarding your first question, It is difficult to tell what the optimizer finds out while compiling the query. I would think that your inner aggregate is executed first.

Regarding your second question, these two alternatives might be easier to understand

SELECT OrderHeader.OrderHeaderID
      ,isNull(Sum(OrderDetail.Qty),0) AS Qty
 FROM OrderHeader
      LEFT JOIN OrderDetail
        ON OrderDetail.OrderHeaderID = OrderHeader.OrderHeaderID
WHERE OrderHeader.Criteria = 'X'
GROUP BY OrderHeader.OrderHeaderID

or

select OrderHeaderId,
       IsNull(
         (Select sum(qty) from OrderDetails d 
           where d.OrderHeaderId = OrderHeader.OrderHeaderId)
         ,0) as qty
  from OrderHeader
 where Criteria='X'

Upvotes: 3

Bohemian
Bohemian

Reputation: 425208

The answer is "yes" - moving a non-key condition of a left-joined table into the ON clause will improve performance because rows will be eliminated from the result as early as possible, rather than leaving the where clause to filter it after all joins are made, especially when further joins are made to other tables.

Upvotes: 1

Andomar
Andomar

Reputation: 238196

The database is free to execute a query however it wants as long as it returns the correct results. That's why SQL is called a declarative language: you specify what you want, not how you want it.

In this case however, the query would change meaning if you moved Criteria = 'X' to the left join. The query would then include OrderHeaders without any OrderDetails. So the database is not free to do the refactoring you suggest.

Upvotes: 1

Related Questions