Which operation will run first in the following query statement?

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;

 

 

 

 

Answer 1

The join of HumanResources.Employee with OrgChart will occur first, then the UNION ALL

---

Vern Rabe

Answer 2

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