SQL Server Interview Question - What are the diferences between INNER JOIN, LEFT JOIN and RIGHT JOIN in SQL Server?

 Posted by ArticlesMaint on 4/2/2011 | Category: Sql Server Interview questions | Views: 3401


Let us Assume we have following two tables:-


Create a new table as"Customers":



























Cust_Id    LastName   FirstName  Address City
1    Shaikh Moosa Churchgate Mumbai
2 Khan Salman Bandra Mumbai
3 Shaikh Feroz Mahim Chennai


Note that the "Cust_Id" column is the primary key in the "Customers" table.
This means that no two rows can have the same Cust_Id.
The Cust_Id distinguishes two persons even if they have the same name.
Next, we have the "Orders" table:




























Order_Id OrderNo   Cust_Id
1 7895 3
2 4678 3
3 2456 1
4 4562 1
5 4764 15


Note that the "Order_Id" column is the primary key in the "Orders" table and that the "Cust_Id" column refers to the persons in the "Customers" table without using their names.


Notice that the relationship between the two tables above is the "Cust_Id" column.


LEFT JOIN:  Return all rows from the left table, even if there are no matches in the right table.
For Example:- 
                    The Following is the example for LEFT JOIN:


Considering the above two tables:


Query:-  Select * from Customers left join Orders on Customers.Cust_Id = Orders.Cust_Id


The output will look like following:

















































Cust_Id LastName  FirstName   Address   City Order_Id OrderNo Cust_Id
1 Shaikh Moosa Churchgate Mumbai 3 2456 1
2 Khan Salman Bandra Mumbai NULL NULL NULL
3 Shaikh Feroz Mahim Chennai 1 7895 3
3 Shaikh Feroz Mahim Chennai 2 4678 3


RIGHT JOIN:  Return all rows from the right table, even if there are no matches in the left table.
For Example:- 
                    The Following is the example for RIGHT JOIN:


Considering the above two tables:


Query:-  Select * from Customers right join Orders on Customers.Cust_Id = Orders.Cust_Id


The output will look like following:


























































Cust_Id LastName FirstName  Address   City   Order_Id OrderNo   Cust_Id
3 Shaikh Feroz Mahim Chennai 1 78958 3
3 Shaikh Feroz Mahim Chennai 2 4678 3
1 Shaikh Moosa Churchgate Mumbai 3 2456 1
NULL NULL NULL NULL NULL 4 4562 4
NULL NULL NULL NULL NULL 5 4764 6


INNER JOIN:  The INNER JOIN keyword return rows when there is at least one match in both tables.
For Example:- 
                   The Following is the example for RIGHT JOIN:
Considering the above two tables:


Query:-  Select * from Customers inner join Orders on Customers.Cust_Id = Orders.Cust_Id


The output will look like following:








































Cust_Id LastName FirstName  Address   City   Order_Id OrderNo   Cust_Id
3 Shaikh Feroz Mahim Chennai 1 78958 3
3 Shaikh Feroz Mahim Chennai 2 4678 3
1 Shaikh Moosa Churchgate Mumbai 3 2456 1


Regards,




Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response