locked
Which operation will run first in the following query statement? RRS feed

  • Question

  •  

     

    Which operation will run first, UNION or INNER JOIN in the following query statement?

    USE AdventureWorks;
    GO
    WITH OrgChart (EmployeeID, ManagerID, Title, Level,Node)
    AS (SELECT EmployeeID, ManagerID, Title, 0,
    CONVERT(VARCHAR(30),'/') AS Node
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT a.EmployeeID, a.ManagerID,a.Title, b.Level + 1,
    CONVERT(VARCHAR(30),b.Node +
    CONVERT(VARCHAR,a.ManagerID) + '/')
    FROM HumanResources.Employee AS a
    INNER JOIN OrgChart AS b ON a.ManagerID = b.EmployeeID
    )
    SELECT EmployeeID, ManagerID, SPACE(Level * 3) + Title AS Title, Level, Node
    FROM OrgChart
    ORDER BY Node;

     

     

     

     

    Sunday, May 8, 2011 11:43 PM

Answers

  • The join of HumanResources.Employee with OrgChart will occur first, then the UNION ALL
    Vern Rabe
    • Proposed as answer by Sali SQL Monday, May 9, 2011 1:45 AM
    • Marked as answer by KJian_ Monday, May 16, 2011 8:26 AM
    Sunday, May 8, 2011 11:54 PM
  • The INNER JOIN has a higher execution priority than UNION ALL as to append two record sets by UNION ALL it is a prerequisite that both the record sets should be in a ready-to-merge state. So, the INNER JOIN of the 2nd tsql will have to be executed first to give out a complete record set before the UNION takes place.

    So, to answer you question, INNER JOIN will be done first and then UNION ALL will take place.


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    • Marked as answer by KJian_ Monday, May 16, 2011 8:26 AM
    Monday, May 9, 2011 6:58 AM

All replies

  • The join of HumanResources.Employee with OrgChart will occur first, then the UNION ALL
    Vern Rabe
    • Proposed as answer by Sali SQL Monday, May 9, 2011 1:45 AM
    • Marked as answer by KJian_ Monday, May 16, 2011 8:26 AM
    Sunday, May 8, 2011 11:54 PM
  • The INNER JOIN has a higher execution priority than UNION ALL as to append two record sets by UNION ALL it is a prerequisite that both the record sets should be in a ready-to-merge state. So, the INNER JOIN of the 2nd tsql will have to be executed first to give out a complete record set before the UNION takes place.

    So, to answer you question, INNER JOIN will be done first and then UNION ALL will take place.


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    • Marked as answer by KJian_ Monday, May 16, 2011 8:26 AM
    Monday, May 9, 2011 6:58 AM