1. Back To Blog

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

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

We are on Social