Join in Sql server.

In this post  I will explain join in Sql Server.

Introduction:-

This is very common question asked in interview. Join is used to retrieve the data from two tables. For retrieving the data from two tables it is necessary that the two tables must have some relation in between them. If tables have relation then it must have primary and foreign key. If there is no relation between two tables then it cannot be retrieve the data from the tables. 

First create two tables in database like as:-

First Table name:- EmpDetail

Key: EmpId is primary key

EmpId	EmpName	Country
1	Sushil kumar	India
2	Pradeep kumar	U.K
3	Sushil Modi	India

Second Table Name:- OrderDetails

Key:- OrderId is primary key and  EmpId is foreign key

OrderId	OrderName	EmpId
1	PC	1
2	Laptop	2
3	Mobile	3
4	HDD	1

There are three types of join.

·         Inner Join

·         Outer Join

·         Self Join

Inner Join:-

Inner Join display only those rows which matched in both the tables. Inner join is also called as default join in sql query .

Syntax For Inner Join:-

Select  tb1.Column_name1 , tb1.Column_name2 , tb2.Column_name1

From Table1 tb1 Inner Join Table2 tb2 on tb1.Column_name=tb2.Column_name

Example:-

select tb1.EmpId,tb1.EmpName,tb2.OrderName

from EmpDetails tb1

inner join OrderDetails tb2

on tb1.EmpId=tb2.EmpId

When you execute this code output like this

EmpId	EmpName	OrderName
1	sushil kumar	PC
2	Pradeep Kumar	Laptop
3	Sushil Modi	Mobile
1	sushil kumar	HDD

Types Of Inner Join:-

·         Equi Join

·         Natural Join

·         Cross Join

Equi Join:-

Equi Join is used to display all matched records from joined tables and also display redundant values. Equi join use * sign to join table.

Syntax for Equi Join:-

select *

from Table1 tb1

inner join Table2 tb2

on tb1. Table1 =tb2. Table2

Example:-

select *

from EmpDetails tb1

inner join OrderDetails tb2

on tb1.EmpId=tb2.EmpId

when you execute this query the output like this

EmpId	EmpName	Country	OrderId	OrderName	EmpId
1	sushil kumar	India	1	PC	1
2	Pradeep Kumar	U.K	2	Laptop	2
3	Sushil Modi	India	3	Mobile	3
1	sushil kumar	India	4	HDD	1

Natural Join:-

Natural join is same as Equi join but only difference is that it restricts to display redundant values.

Syntax For Natural Join:-

select *

from Table1 tb1

Natural  Table2 tb2

on tb1. Table1 =tb2. Table2

This syntax will give error because it will not work in Sql Server . It will work in Oracle.

Cross Join:-

  It gives the cartesian product of two join tables and the size of cartesian product is equal to the multiplication of total no of row of first table and total no of row of second table.

Syntax For Cross Join:-

select  *

from Table1

Cross join Table2

Example:-

select  *

from EmpDetails   

Cross join OrderDetails  

When You will execute this query the output will be like this..

EmpId	EmpName	Country	OrderId	OrdernName	EmpId
1	sushil kumar	India	1	PC	1
1	sushil kumar	India	2	Laptop	2
1	sushil kumar	India	3	Mobile	3
1	sushil kumar	India	4	HDD	1
2	Pradeep Kumar	U.K	1	PC	1
2	Pradeep Kumar	U.K	2	Laptop	2
2	Pradeep Kumar	U.K	3	Mobile	3
2	Pradeep Kumar	U.K	4	HDD	1
3	Sushil Modi	India	1	PC	1
3	Sushil Modi	India	2	Laptop	2
3	Sushil Modi	India	3	Mobile	3
3	Sushil Modi	India	4	HDD	1

OuterJoin:-

OuterJoin returns all the row which are matched and unmatched from the joined table .

There are three types of OuterJoin:-

·         Left Outer Join

·         Right Outer join

·         Full Outer Join

Left Outer Join:-

Left Outer Join display all the row form first table and matched row from the scond table.

Syntax for Left Outer Join:-

select  Column_list  from Table1 tb1

LEFT OUTER JOIN Table2 tb2

on tb1. Column_name =tb2. Column_name

Example:-

select  tb1.EmpName, tb1.Country from EmpDetails tb1

LEFT OUTER JOIN OrderDetails tb2

on tb1. EmpId =tb2. EmpId

After executing the query output will be like this.

EmpName	Country	OrderName
sushil kumar	India	PC
sushil kumar	India	HDD
Pradeep Kumar	U.K	Laptop
Sushil Modi	India	Mobile

Right Outer Join:-

Right Outer Join Display all the row from right table and matched row from left table.

Syntax for Left Outer Join:-

select  Column_list  from Table1 tb1

RIGHT OUTER JOIN Table2 tb2

on tb1. Column_name =tb2. Column_name

Example:-

select  tb1.EmpName, tb1.Country from EmpDetails tb1

RIGHT OUTER JOIN OrderDetails tb2

on tb1. EmpId =tb2. EmpId

After executing the query output will be like this.

EmpId	EmpName	Country	OrderName
1	sushil kumar	India	PC
2	Pradeep Kumar	U.K	Laptop
3	Sushil Modi	India	Mobile
1	sushil kumar	India	HDD

Full Outer Join:-

Full Outer join deisply all the matching and non-matching records from join table.

Synatx for Full Outer Join:-

select  Column_list  from Table1 tb1

RIGHT OUTER JOIN Table2 tb2

on tb1. Column_name =tb2. Column_name

Example:-

select  tb1.EmpName, tb1.Country from EmpDetails tb1

FULL  OUTER JOIN OrderDetails tb2

on tb1. EmpId =tb2. EmpId

After executing the query output will be like this.

EmpId	EmpName	Country	OrederName
1	sushil kumar	India	PC
1	sushil kumar	U.K	HDD
2	Pradeep Kumar	India	Laptop
3	Sushil Modi	India	Mobile

Self Join:-

Joining the table itself is called as Self Join. Self join ise used if the table have a relation with other  record in the same table.

First Make Table EmpDetails in which the fields name are EmpId,EmpName and ManagerId like this.

EmpId	EmpName	ManagerId
1	Sushil Kumar	2
2	Pradeep Kumar	4
3	Ram	7
4	Krishan	1
6	Shyam	3
7	Mohan	2

Example:-

select tb2.EmpName,tb1.EmpName as 'Manager'

 from EmpDetails tb1

inner join EmpDetails tb2

on tb1.EmpId=tb2.ManagerId

After executing the query output will be like this.

EmpName	ManagerId
Krishan	Sushil Kumar
Sushil Kumar	Pradeep Kumar
Mohan	Pradeep Kumar
Shyam	Ram
Pradeep Kumar	Krishan
Ram	Mohan