SQL SERVER – Effect of Order of Join In Query

Let us try to understand this subject with example.

We will use Adventurworks database for this purpose. Table which we will be using are HumanResources.Employee (290 rows), HumanResources.EmployeeDepartmentHistory (296 rows) and HumanResources.Department (16 rows).

We will be running following two queries and observe the output. In the resultset the order of first column (EmployeeID) is different in both the cases when whole resultset is same. When compared both the results they are same but the order of rows is different in both the resultset.

Query 1 :

SELECT he.EmployeeID, he.Title, hd.Name, hd.GroupName, hdh.StartDate
FROM HumanResources.Employee he
LEFT JOIN HumanResources.EmployeeDepartmentHistory hdh
ON he.EmployeeID = hdh.EmployeeID
RIGHT JOIN HumanResources.Department hd
ON hd.DepartmentID = hdh.DepartmentID

Query 2:

SELECT he.EmployeeID, he.Title, hd.Name, hd.GroupName, hdh.StartDate
FROM HumanResources.Employee he
INNER JOIN HumanResources.EmployeeDepartmentHistory hdh
ON he.EmployeeID = hdh.EmployeeID
INNER JOIN HumanResources.Department hd
ON hd.DepartmentID = hdh.DepartmentID

Now the interesting part of understand why the order is different.

I would like my readers and participate here and give me their opinion. There are many reasons for this happening but what I think is “Effect of Index”.

When we use Left or Right Join, We have a base table Employee and the records are order by the primary key i.e The EmployeeID of the base table by default. But when we use the Inner Join, then the table having smallest number of records are used to order by. Here in our above example, the HumanResources.Department has 16 Records. So  the records are sorted by the departmentId of the HumanResources.Department table.

Let me know your opinion and also let me know if you like this kind of small articles.

Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)