Left Outer Join

Question

Hi:

Pardon me for asking this basic question but I am wondering using a Left outer join if we can achieve something that cannot be achieved absolutely using a right outer join. I am looking for a scenario where in only a left outer join can be used and the same result cannot be achieved using a right outer join. In other words left outer join outperforms right outer join.

An example script would be great.

Experts -Please advice.

Thank you

Ankith

 

Answer 1

If you change the order of the tables, a LEFT JOIN is functionally equilivent to a RIGHT JOIN. If you don't change the order of the tables, they are very different.

For example:

USE Northwind

SELECT
   e.EmployeeID,
   e.LastName,
   o.OrderID
FROM Employees e
   LEFT JOIN Orders o
      ON e.EmployeeID = o.EmployeeID

SELECT
   e.EmployeeID,
   e.LastName,
   o.OrderID
FROM Orders o
   RIGHT JOIN Employees e
      ON e.EmployeeID = o.EmployeeID

-- Both LEFT AND RIGHT JOINS return the same data. In fact, if you examine the execution plan, you will notice that the query processor converted the RIGHT JOIN into a LEFT JOIN -BOTH plans are identical.

 

 

Answer 2

Thanks Arnie. However I do not want to change the order of tables. All I am looking is for a case where you can use only left join and right outer join is not at all possible. something that can be achieved using only the left join. Is it actually feasible.?My guess it is not unless some one can show me otherwise.

Thanks

Ankith

 

Answer 3

I would say no, because A left join B is symantically equivalent to B right join A. (there is a slight difference in the output, in that columns would be put into the set in a different order,  but even then A.*, B.* has all of the same stuff as B.*, A.*, just in a different order, which is not significant in SQL.

Answer 4

Thanks Louis for confirming that.