Explain join in SQL Server ? (SQL Server interview questions)
- By Shiv Prasad Koirala in SQL
- Apr 5th, 2014
- 35551
- 0
Joins let us retrieve records from multiple together by combining them with the help some logical condition.
For understanding joins better let's talk in terms of examples. We have four kinds of join inner join, outer join, cross join.
Let's assume we have two tables.
TblCustomer
CustomerId |
CustomerName |
CityId |
1 |
Customer1 |
C1 |
2 |
Customer2 |
C1 |
3 |
Customer3 |
C2 |
4 |
Customer4 |
C3 |
5 |
Customer5 |
NotSpecified |
TblCity
CityId |
CityName |
C1 |
City1 |
C2 |
City2 |
C3 |
City3 |
C4 |
City4 |
Types of Joins
1. Inner Join - Join both tables using specified condition and return matching records.
Example
Query - Select CustomerName,CityName from TblCustomer inner join TblCity on TblCustomer.CityId=TblCity.CityId
Output -
CustomerName |
CityName |
Customer1 |
City1 |
Customer2 |
City1 |
Customer3 |
City2 |
Customer4 |
City3 |
2. Outer Join - Join both tables using specified condition and returns,
a. matching records
b. Unmatching records from either left, right or both tables based on subtype of outer join we have used. There are three sub types Left outer Join, Right Outer Join and Full Outer Join.
i. Left Outer Join
Query - Select CustomerName,CityName from TblCustomer left outer join TblCity on TblCustomer.CityId=TblCity.CityIdOutput -
Output -
CustomerName |
CityName |
Customer1 |
City1 |
Customer2 |
City1 |
Customer3 |
City2 |
Customer4 |
City3 |
Customer5 |
Null |
i. Right Outer Join
Query - Select CustomerName,CityName from TblCustomer Right outer join TblCity on TblCustomer.CityId=TblCity.CityId
Output -
CustomerName |
CityName |
Customer1 |
City1 |
Customer2 |
City1 |
Customer3 |
City2 |
Customer4 |
City3 |
Null |
City 4 |
i. Full Outer Join
Query - Select CustomerName,CityName from TblCustomer Full outer join TblCity on TblCustomer.CityId=TblCity.CityId
Output -
CustomerName |
CityName |
Customer1 |
City1 |
Customer2 |
City1 |
Customer3 |
City2 |
Customer4 |
City3 |
Null |
City4 |
Customer5 |
Null |
3. Cross Join - Join every row of one table to every row of second table and return Cartesian product.
Note:
i. No special join keyword is required in the query.
ii. No where condition is required.
Query - Select CustomerName,CityName from TblCustomer,TblCity
Output -
CustomerName |
CityName |
Customer1 |
City1 |
Customer1 |
City2 |
Customer1 |
City3 |
Customer1 |
City4 |
Customer2 |
City1 |
Customer2 |
City2 |
Customer2 |
City3 |
Customer2 |
City4 |
Customer3 |
City1 |
Customer3 |
City2 |
Customer3 |
City3 |
Customer3 |
City4 |
Customer4 |
City1 |
Customer4 |
City2 |
Customer4 |
City3 |
Customer4 |
City4 |
Customer5 |
City1 |
Customer5 |
City2 |
Customer5 |
City3 |
Customer5 |
City4 |
Here is a nice video created by www.questpond.com which explains SQL Server joins with a practical demonstration.
For more such tips and tricks subscribe us on Facebook.
Do not miss to watch our SQL Server Interview Questions video with sample answers :-
Shiv Prasad Koirala
Visit us @ www.questpond.com or call us at 022-66752917... read more