Back To Blog

Explain join in SQL Server ? (SQL Server interview questions)

By Shiv Prasad Koirala in SQL
Apr 5th, 2014
35598
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