LEFT OUTER JOIN

RIGHT OUTER JOIN

LEFT OUTER JOIN / LEFT JOIN returns all the rows from the LEFT table and the corresponding matching rows from the right table. If right table doesn’t have the matching record then for such records right table column will have NULL value in the result.

RIGHT OUTER JOIN / RIGHT JOIN returns all the rows from the RIGHT table and the corresponding matching rows from the left table. If left table doesn’t have the matching record then for such records left table column will have NULL value in the result.

As per the above LEFT OUTER JOIN Venn Diagram, all the records from the left table (Customers) are returned regardless if any of those records have a match in the right table (Orders). It will also return any matching records from the right table (Orders)

As per the above RIGHT OUTER JOIN Venn Diagram, all the rows in the right table (Orders) are returned regardless if any of those records have a match in the left table (Customers). It will also return any matching records from the left table (Customers)

As per the data in our demo tables, Customers with CustomerId 1 and 3 in Customers table have the orders in the Orders table. Where as the customer with CustomerId 2 doesn’t have any order in the Orders table. So the LEFT JOIN on the CustomerId column between Customers and Orders table will return the Customer and Order details of the Customers with CustomerId 1 and 3 and for CustomerId 2 the Order Table columns will have NULL value in the result. Let us verify this by executing the following LEFT OUTER JOIN query:

SELECT *
FROM Customers C 
  LEFT OUTER JOIN Orders O
    ON O.CustomerId = C.CustomerId

RESULT:

As per the data in our demo tables, only for the order with OrderId 200 we don’t have it’s corresponding customer info with CustomerId 4 in the Customers table. And for the other two orders, the corresponding customer info is present in the Customers Table. So for the orders with CustomerId 1 and 3 will have customer details and for the order with CustomerId 4, the Customers table columns will have NULL value in the result. Let us verify this by executing the following RIGHT OUTER JOIN query:

SELECT *
FROM Customers C 
  RIGHT OUTER JOIN Orders O
    ON O.CustomerId = C.CustomerId

RESULT

SELECT *
FROM Customers C 
  LEFT OUTER JOIN Orders O
    ON O.CustomerId = C.CustomerId

From the execution plan we can see that first all records from the LEFT TABLE (i.e. Customers) of the JOIN clause are fetched. Then for each of these LEFT TABLE (i.e. Customers) records, it is trying to fetch the matching data from the RIGHT TABLE (i.e. Orders TABLE) using Nested Loops LEFT OUTER JOIN operation.

SELECT *
FROM Customers C 
  RIGHT OUTER JOIN Orders O
    ON O.CustomerId = C.CustomerId

RESULT:

From the execution plan we can see that first all records from the RIGHT TABLE (i.e. Orders) of the JOIN clause are fetched. Then for each of these LEFT TABLE (i.e. Orders) records, it is trying to fetch the matching data from the RIGHT TABLE (i.e. Customers TABLE) using Nested Loops LEFT OUTER JOIN operation. From the execution plan we can observe that Sql Server indirectly converted this RIGHT OUTER JOIN query to a LEFT OUTER JOIN query like below before execution:

SELECT *
FROM Orders O 
  LEFT OUTER JOIN Customers O
    ON O.CustomerId = C.CustomerId 

So, to get the result from LEFT OUTER JOIN which is same as that of RIGHT OUTER JOIN and vice-versa, we just need to switch the order in which the tables appear in the JOIN clause.