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
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 (http://blog.SQLAuthority.com)