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

Answer:

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,

Please click here to see more SQL Server interview questions

Advertisements

About c# and .NET Interview questions

This blog is for developers who want to crack .NET and C# interviews. It has all tips and tricks needed to crack .NET interviews , C# interview , SQL Server interview , Java interview , WCF Interview , Silverlight interview , WPF interview , LINQ interview , Entity framework Interview. Do not forget to watch our Learn step by step video series. Learn MVC in 16 hours:- https://www.youtube.com/watch?v=Lp7nSImO5vk Learn AngularJS Step by Step:- https://www.youtube.com/watch?v=0kmdjqgO9IY Learn Design Pattern in 8 hours:- https://www.youtube.com/watch?v=YDobmucohqk Learn C# and .NET in 60 days:- https://www.youtube.com/watch?v=yh2SrzCkNQA Learn MSBI in 32 hours:- https://www.youtube.com/watch?v=mGPJx3ocFgg Learn SharePoint Step by Step in 8 hours:- https://youtu.be/C2fW76SwJNU
This entry was posted in Uncategorized and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s