Joins are use to extract data from more than one tables based on primary key and foreign key relationship between tables. Basically joins are used to generate reports on daily basis.

Type of joins in Sql server

Inner Join also known as Equi Join

Outer Join

Cross Join or Cross Product like 2 x 3 =6

Outer Joins are further divided into Left Outer Join and Right Outer Join.

We have created two dummy tables with primary and foreign key concept to demonstrate joins based on dummy data which we are inserting using below script in the article.

 

if exists (select * from sys.sysobjects where name = ’emp_details’ and type = ‘U’)
begin
drop table emp_details
end
else
begin
create table emp_details
( emp_id int primary key,
emp_name varchar(50),
emp_add varchar(20),
emp_deg varchar(20) )
end

 

if exists (select * from sys.sysobjects where name = ’emp_project_info’ and type = ‘U’)
begin
drop table emp_project_info
end
else
begin

create table emp_project_info
( emp_id int REFERENCES emp_details(emp_id),
emp_name varchar(50),
Project_Assign varchar(20),
Assign_date varchar(20),
End_date varchar(20) )

end

 

Begin transaction
— Not added in primary but added in secondary table

insert into emp_details values(100021,’Arun’,’NJ’,’DBA’)
insert into emp_details values(100022,’Shamesh’,’JN’,’Sr.DBA’)
insert into emp_details values(100023,’Ramaya’,’CL’,’Sr.DBA’)
insert into emp_details values(100024,’Sandy’,’AB’,’Sr.BI’)

 

— secondary table insert

into emp_project_info values(100012,’Suresh’,’AG’,’24/09/2011′,’30/12/2011′)
insert into emp_project_info values(100013,’Shirti’,’GE’,’24/09/2011′,’30/12/2011′)
insert into emp_project_infovalues(100014,’Ramesh’,’CG’,’24/09/2011′,’30/12/2011′)
insert into emp_project_infovalues(100012,’Suresh’,’AG’,’24/09/2011′,’30/12/2011′)
Insert into emp_project_infoValues(100015,’Sanjay’,null,null,null)

 

Commit transaction

 

select * from emp_details

select * from emp_project_info

 

Inner Join

Inner Join Display all matching rows from both tables based on key column. Use to display matching rows from two different tables in sql server.

Example:

select * from emp_details a inner join emp_project_info b

on a.emp_id = b.emp_id

and b.emp_id = a.emp_id

 

Left Outer Join

Use to display all rows from first table(left side) of the join in sql server 2005 and matching rows from second table.

Example:

select * from emp_details a left outer join emp_project_info b

on a.emp_id = b.emp_id

and b.emp_id = a.emp_id

 

 

Right outer join

Use to display all rows from second table(right side) of the join in sql server 2005 and matching rows from first table.

Example:

select * from emp_details a Right outer join emp_project_info b
on a.emp_id = b.emp_id
and b.emp_id = a.emp_id

 

 

Full outer join

It will display all rows from both tables of the join in sql server 2005, it will display NULL for non matching rows.

Example:
select * from emp_details a Full outer join emp_project_info b
on a.emp_id = b.emp_id
and b.emp_id = a.emp_id

 

 

Cross join

It will multiple each rows of first table with all rows from second table, it will display NULL for non matching rows.

Example:
select * from emp_details a Cross join emp_project_info b

Output

emp_details – 8 rows

emp_project_info – 4 rows

Total 32 rows