.Net Interview Questions and Answers

Search:

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

By : Shiv Prasad Koirala | Apr 5th, 2014 | Views : 2711 | Comments : 0 Bookmark and Share

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


Below 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.


For technical training on various topics like SQL, WCF, Business Intelligence, MVC, Design patterns and many more contact www.Sukesh-Marla.com or contact SukeshMarla@Gmail.com

 
Rate this Article :
1 Star
2 Stars
3 Stars
4 Stars
5 Stars
1 1 1 2 1
 
 
 
 

.NET interview questions 6th edition (Sixth edition) - By Shivprasad Koirala

In this article we will explain .NET interview questions 6th edition (Sixth edition) - By Shivprasad Koirala.... Read More

What is the difference between Stored Procedures and Function (SQL Server interview question with answers)?

In this article we will explain what is the difference between Stored Procedures and Function.For more articles and videos visit us on www.questpond.com... Read More

Explain MVC application life cycle?

In this article we will explain MVC application life cycle.For more articles and videos visit us on www.questpond.com... Read More

C# Design pattern interview questions with answers: - How Singleton is different from Static class?

In this article we will explain How Singleton is different from Static class.... Read More

WCF interview questions and answers: - What is the difference between Service endpoint and Client endpoint?

In this article we will explain What is the difference between Service endpoint and Client endpoint.... Read More

.NET interview questions 6th edition (Sixth edition) - By Shivprasad Koirala

In this article we will explain .NET interview questions 6th edition (Sixth edition) - By Shivprasad Koirala.... Read More

What is the difference between Stored Procedures and Function (SQL Server interview question with answers)?

In this article we will explain what is the difference between Stored Procedures and Function.For more articles and videos visit us on www.questpond.com... Read More

Explain MVC application life cycle?

In this article we will explain MVC application life cycle.For more articles and videos visit us on www.questpond.com... Read More

C# Design pattern interview questions with answers: - How Singleton is different from Static class?

In this article we will explain How Singleton is different from Static class.... Read More

WCF interview questions and answers: - What is the difference between Service endpoint and Client endpoint?

In this article we will explain What is the difference between Service endpoint and Client endpoint.... Read More

Article Categories

Learn in Hindi