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