Left and Right Join in SQL Server 2008

Joining in SQL means to fetch data from more than 1 table.

Joining in tables does not mean that the tables must have a primary and foreign key relationship.

Now suppose I have 2 tables.

1. Customers
   
   
    
2. tblorders
   
   
   

See in the Customer table we have 10 records and in the tblorders table we have 8 records.

Now the Requirements is:

1. Display all the Order Names and the particular Customer name that has the Orders in the order table.
2. Display the whole Order Name from the order details table along with the Customer Name that has the order in the Orders Table

Now for the first requirements we go for LEFT OUTER JOIN.

SQL LEFT JOIN
 
The LEFT JOIN keyword returns all rows from the left table (Customers), even if there are no matches in the right table (tblOrders).

1. Customers
   
   
    
2. tblorders
   
   
   

So here it will display all the records of the customers table along with the matching "CustId" in table Orders. If any "Cust Id" does not match in the "tblOrders" It will be null for that particular part.

Now see the query:

select cust.Amount,cust.CustomerName,cust.CustId,ordr.CustId,ordr .OrderName  from dbo.Customer as cust Left join
 dbo.tblOrder as ordr on cust.CustId =ordr .CustId

Now run the query; it will look like:



See here from the Customer table all the 11 records are displaying along with the customer that has the order in order table. But the customers who don't have an order in the orders table i.e. the "Cust Id" is not matching are displaying here NULL Values. See I marked with red.

That means that in the Orders table we don't have the Cust id 6,7,9,10,11.

Now for the Second Requirements

"Display the whole Order Name from the order details table along with the Customer Name that has the order in the Orders Table".
For this we have to do RIGHT OUTER JOIN.

SQL RIGHT JOIN

The RIGHT JOIN keyword returns all rows from the Right table (tblOrders), even if there are no matches in the Left table (Customers).
It is the opposite of Left Outer join.

1. Customers
   
   
    
2. tblorders
   
   
   

So here it will display all the records of the Orders table along with the matching "CustId" in the Customer Tables. If any "Cust Id" does not match in the "Customers" table then it will show null for that particular part.

Now see the query:

select cust.Amount,cust.CustomerName,cust.CustId,ordr.OrderId,ordr .OrderName  from dbo.Customer as cust Right join dbo.tblOrder as ordr on cust.CustId =ordr .CustId

See here I used the "Right Join" keyword.

Run the query; it will look like:



See here all the records of the orders table are displaying (marked with red) along with all CustomerNames with matching "CustId" in the "Orders" Table.

See here the OrderId 7 and 8 are displaying Null (marked with blue) values in the Customer Tables. That means there are no matching "CustId" in the "Orders" table.

Conclusion: In this article I described the basic idea of left and right outer joins.