Good Evening friends..Today I am going to talk about types of joins supported by SQL Server. I got a comment from somebody requesting for putting an article on Joins. Hence I thought to write an article on Joins.
Why do we need joins :- I have two different tables and below is the specification of two tables.
First Table :- Emp, which contains two columns Eid and Name.
Second Table:-Vehicledetail, which also contains two columns Eid and VehicleName.
In Emp table we have stored employee data(name and employee id) and in Vehicledetail table we have stored the information about those employees who all are having any vehicle with them. Now if I want to get the list of employees and their vehicle name I have to join these two tables based on Eid(the only common attribute in both tables) to get the required detail.
Types of Join in SQL Server :-
Create table Emp(Eid int,Name varchar(50))
Create table VehicleDetail(Eid int,VehicleName varchar(50))
insert into emp values(101,'Neeraj')
insert into emp values(102,'Rachit')
insert into emp values(103,'Neha')
insert into emp values(104,'Shivika')
insert into emp values(105,'Akshay')
Insert into VehicleDetail values(101,'Scooty')
Insert into VehicleDetail values(103,'Car')
Insert into VehicleDetail values(105,'Bike')
Insert into VehicleDetail values(107,'Zipsy')
Syntax of Inner join :-
Select Emp.Eid,Name,Vehiclename from emp
inner join Vehicledetail on emp.Eid=Vehicledetail.eid
Inner join can also be written as
Select Emp.Eid,Name,Vehiclename from emp,Vehicledetail where emp.Eid=Vehicledetail.eid
If we run above query the result would be as shown in the below image.
We have joined both tables Emp and Vehicledetail based on common column Eid and as a result it is showing three rows because we have only 3 values of Eid column, common in both tables.
Common values for Eid columns are(101,103,105) while Eid(102,104) are only present in table Emp not in
Vehicledetail table, similarly Eid (107) is present in Vechicledetail table but not in Emp table. I hope this example will give you clear picture of inner join.
Left join or Left Outer Join :- In left join we take all the records from the left table and get matching data(based on joining column) from the right table. If there are some values in the left table which are not present in right table it will show Null values and if there are some values in the joining column those are present in right table but not in Left table, those values will be ignored.Lets take another example to understand it completely.
Syntax of Left Join :-
select Emp.Eid,Name,Vehiclename from emp
Left join Vehicledetail on emp.Eid =vehicledetail.eid
Left join can also be written as
select Emp.Eid,Name,Vehiclename from emp
Left outer join Vehicledetail on emp.Eid =vehicledetail.eid
Left join can also be written as
select Emp.Eid,Name,Vehiclename from emp,Vehicledetail
where emp.Eid *=vehicledetail.eid--> Only supported in SQL 2000 and earlier versions
In the result we can see that we have all the Eid (101,102,103,104,105) from Emp table and Vehiclename from Vehicledetail table, Since we have only 3 matching values for Eid column in Vechicledetail table(101,103,105), hence we got vehiclename from Vehicledetail table for these 3 values but Eid(102,104) are not present in table VehicleDetail hence we got Null value for these Eid's. and EID 107 is present in Vehicledetail but not in Emp hence it is ignored.
Note:- Table name highlighted with Yellow color is left table and table name highlighted with pink color is right table.
Right Join or Right Outer Join:- In Right join we take all the records from the right table and get matching data(based on joining column) from the Left table. If there are some values in the Right table which are not present in left table it will show Null values and if there are some values in the joining column those are present in left table but not right table, those values will be ignored.Lets take another example to understand it completely.
Syntax of Right Join or Right Outer Join:-
select Vehicledetail.Eid,Name,Vehiclename from emp
Right outer join Vehicledetail on emp.Eid =vehicledetail.eid
Right join can also be written as
select Vehicledetail.Eid,Name,Vehiclename from emp
Right join Vehicledetail on emp.Eid =vehicledetail.eid
Right join can also be written as
select Vehicledetail.Eid,Name,Vehiclename
from emp,Vehicledetail Where emp.Eid =*vehicledetail.eid
In the result we can see that we have all the Eid (101,103,105,107) from Vehicledetail table and name from Emp table, Since we have only 3 matching values for Eid column in Emp table(101,103,105), hence we got name from Emp table for these 3 values but Eid(107) is not present in table Emp hence we got Null value for these Eid= 107. And EID 102,104 are present in Emp but not in Vehicledetail hence these are ignored.
Note:- Table name highlighted with Yellow color is left table and table name highlighted with pink color is right table.
Full Outer Join :- In full outer Join, as a result set, we get combination of result from left outer join and right outer join. Lets take an example to understand it clearly.
Syntax of Full Outer Join:-
select Vehicledetail.Eid,Name,Vehiclename from emp
full outer join Vehicledetail on emp.Eid =vehicledetail.eid
Full outer join can also be written as
select Vehicledetail.Eid,Name,Vehiclename from emp
full join Vehicledetail on emp.Eid =vehicledetail.eid
We have Eid values(101,102,103,104,105) in table Emp and Eid values(101,103,105,107) in table Vehicledetail, Hence we have only 5+1 =6 unique values of Eid in both tables, and if we join both tables using full outer join it will return 6 rows.
Hence Formula for full outer join will be :- No of rows return by the query would be= no of unique values from both tables of joining column.
Note:- This formula is only applicable if both tables are not having any duplicate values for joining column.
Lets have a look on the result set shown in the above image, Since full outer join is the combination of result set of left join and right join, Please see below result to understand completely.
select Vehicledetail.Eid,Name,Vehiclename from emp
left join Vehicledetail on emp.Eid =vehicledetail.eid
Union
select Vehicledetail.Eid,Name,Vehiclename from emp
Right join Vehicledetail on emp.Eid =vehicledetail.eid
In the above image you can see that I have written left join query and right join query and combined the result set using union operator and you can see that result is same as we got the result by using full outer join.
Cross Join :- Cross join returns the Cartesian product as a result set. Lets understand this statement by taking an example. We have 5 records in Emp table and 4 records in Vehicledetail table and Cartesian product would be 5*4=20 records.
Syntax of Full Outer Join:-
select Vehicledetail.Eid,Name,Vehiclename from emp
Cross Join Vehicledetail
It means each Eid from Emp table will be joined with each record of Eid from Vehicledetail table.
lets take a look what result comes up after applying cross join.
You can see in the above image all Eids(101,102,103,104,105) from the Emp table getting repeated 4 times as we have 4 records in the Vehicledetail table.
first record of Emp table is Eid=101 and Name=Neeraj which is getting combined with each record of Vehicledetail, as a result we are getting result set encircled by red color in the previous image. similarly second record in the table is Eid=102 and Name =Rachit and as a result we are getting result set encircled by blue color. Same way each record of Emp table is getting combined with each record in the Vehicletable.
I hope this article will give you insight to Joins in SQL Server. Please feel free to ask any question related to Joins. Have a great day.
Why do we need joins :- I have two different tables and below is the specification of two tables.
First Table :- Emp, which contains two columns Eid and Name.
Second Table:-Vehicledetail, which also contains two columns Eid and VehicleName.
In Emp table we have stored employee data(name and employee id) and in Vehicledetail table we have stored the information about those employees who all are having any vehicle with them. Now if I want to get the list of employees and their vehicle name I have to join these two tables based on Eid(the only common attribute in both tables) to get the required detail.
Types of Join in SQL Server :-
- Inner Join
- Left Join or Left Outer Join
- Right Join or Right Outer Join
- Full Outer Join
- Cross Join
Create table Emp(Eid int,Name varchar(50))
Create table VehicleDetail(Eid int,VehicleName varchar(50))
insert into emp values(101,'Neeraj')
insert into emp values(102,'Rachit')
insert into emp values(103,'Neha')
insert into emp values(104,'Shivika')
insert into emp values(105,'Akshay')
Insert into VehicleDetail values(101,'Scooty')
Insert into VehicleDetail values(103,'Car')
Insert into VehicleDetail values(105,'Bike')
Insert into VehicleDetail values(107,'Zipsy')
Syntax of Inner join :-
Select Emp.Eid,Name,Vehiclename from emp
inner join Vehicledetail on emp.Eid=Vehicledetail.eid
Inner join can also be written as
Select Emp.Eid,Name,Vehiclename from emp,Vehicledetail where emp.Eid=Vehicledetail.eid
If we run above query the result would be as shown in the below image.
We have joined both tables Emp and Vehicledetail based on common column Eid and as a result it is showing three rows because we have only 3 values of Eid column, common in both tables.
Common values for Eid columns are(101,103,105) while Eid(102,104) are only present in table Emp not in
Vehicledetail table, similarly Eid (107) is present in Vechicledetail table but not in Emp table. I hope this example will give you clear picture of inner join.
Left join or Left Outer Join :- In left join we take all the records from the left table and get matching data(based on joining column) from the right table. If there are some values in the left table which are not present in right table it will show Null values and if there are some values in the joining column those are present in right table but not in Left table, those values will be ignored.Lets take another example to understand it completely.
Syntax of Left Join :-
select Emp.Eid,Name,Vehiclename from emp
Left join Vehicledetail on emp.Eid =vehicledetail.eid
Left join can also be written as
select Emp.Eid,Name,Vehiclename from emp
Left outer join Vehicledetail on emp.Eid =vehicledetail.eid
Left join can also be written as
select Emp.Eid,Name,Vehiclename from emp,Vehicledetail
where emp.Eid *=vehicledetail.eid--> Only supported in SQL 2000 and earlier versions
In the result we can see that we have all the Eid (101,102,103,104,105) from Emp table and Vehiclename from Vehicledetail table, Since we have only 3 matching values for Eid column in Vechicledetail table(101,103,105), hence we got vehiclename from Vehicledetail table for these 3 values but Eid(102,104) are not present in table VehicleDetail hence we got Null value for these Eid's. and EID 107 is present in Vehicledetail but not in Emp hence it is ignored.
Note:- Table name highlighted with Yellow color is left table and table name highlighted with pink color is right table.
Right Join or Right Outer Join:- In Right join we take all the records from the right table and get matching data(based on joining column) from the Left table. If there are some values in the Right table which are not present in left table it will show Null values and if there are some values in the joining column those are present in left table but not right table, those values will be ignored.Lets take another example to understand it completely.
Syntax of Right Join or Right Outer Join:-
select Vehicledetail.Eid,Name,Vehiclename from emp
Right outer join Vehicledetail on emp.Eid =vehicledetail.eid
Right join can also be written as
select Vehicledetail.Eid,Name,Vehiclename from emp
Right join Vehicledetail on emp.Eid =vehicledetail.eid
Right join can also be written as
select Vehicledetail.Eid,Name,Vehiclename
from emp,Vehicledetail Where emp.Eid =*vehicledetail.eid
In the result we can see that we have all the Eid (101,103,105,107) from Vehicledetail table and name from Emp table, Since we have only 3 matching values for Eid column in Emp table(101,103,105), hence we got name from Emp table for these 3 values but Eid(107) is not present in table Emp hence we got Null value for these Eid= 107. And EID 102,104 are present in Emp but not in Vehicledetail hence these are ignored.
Note:- Table name highlighted with Yellow color is left table and table name highlighted with pink color is right table.
Full Outer Join :- In full outer Join, as a result set, we get combination of result from left outer join and right outer join. Lets take an example to understand it clearly.
Syntax of Full Outer Join:-
select Vehicledetail.Eid,Name,Vehiclename from emp
full outer join Vehicledetail on emp.Eid =vehicledetail.eid
Full outer join can also be written as
select Vehicledetail.Eid,Name,Vehiclename from emp
full join Vehicledetail on emp.Eid =vehicledetail.eid
We have Eid values(101,102,103,104,105) in table Emp and Eid values(101,103,105,107) in table Vehicledetail, Hence we have only 5+1 =6 unique values of Eid in both tables, and if we join both tables using full outer join it will return 6 rows.
Hence Formula for full outer join will be :- No of rows return by the query would be= no of unique values from both tables of joining column.
Note:- This formula is only applicable if both tables are not having any duplicate values for joining column.
Lets have a look on the result set shown in the above image, Since full outer join is the combination of result set of left join and right join, Please see below result to understand completely.
select Vehicledetail.Eid,Name,Vehiclename from emp
left join Vehicledetail on emp.Eid =vehicledetail.eid
Union
select Vehicledetail.Eid,Name,Vehiclename from emp
Right join Vehicledetail on emp.Eid =vehicledetail.eid
In the above image you can see that I have written left join query and right join query and combined the result set using union operator and you can see that result is same as we got the result by using full outer join.
Cross Join :- Cross join returns the Cartesian product as a result set. Lets understand this statement by taking an example. We have 5 records in Emp table and 4 records in Vehicledetail table and Cartesian product would be 5*4=20 records.
Syntax of Full Outer Join:-
select Vehicledetail.Eid,Name,Vehiclename from emp
Cross Join Vehicledetail
It means each Eid from Emp table will be joined with each record of Eid from Vehicledetail table.
lets take a look what result comes up after applying cross join.
You can see in the above image all Eids(101,102,103,104,105) from the Emp table getting repeated 4 times as we have 4 records in the Vehicledetail table.
first record of Emp table is Eid=101 and Name=Neeraj which is getting combined with each record of Vehicledetail, as a result we are getting result set encircled by red color in the previous image. similarly second record in the table is Eid=102 and Name =Rachit and as a result we are getting result set encircled by blue color. Same way each record of Emp table is getting combined with each record in the Vehicletable.
I hope this article will give you insight to Joins in SQL Server. Please feel free to ask any question related to Joins. Have a great day.
No comments:
Post a Comment