Types of joins in sql server

In this article i explain about joins in sql server. Join is used to combine two or more tables with common relationship between tables. There are various types of joins like inner join, outer join, cross join in sql server. If we want to select data from two different tables we can use these joins.

Join is used to combine two or more tables in sql with some relationship between tables. There are several types of joins in sql server. The most common join used in pratical are Inner Join, Outer Join, Cross Join.

Let us consider these two tables to know how to use joins in tables.


Detail Table:

Id	Name	PhoneNo
1	Ramesh	9786756453
2	Siva	9283746565
3	Kamal	9192939495

Address Table:


Sno	Resident	Office
1	Trichy		Delhi
2	Madurai	        Chennai
4	Coimbatore	Banglore

Inner Join:

Inner join return the rows when the first table have the exact match with the second table with atleast any one common value.


Query:

select a.Id, b.Sno, a.Name, a.PhoneNo, b.ResidentAddress, b.OfficeAddress 
from Detail a inner join Address b on a.Id = b.Sno


Output:

Id	Sno	Name	PhoneNo	    Resident   Office
1	1	Ramesh	9786756453   Trichy	 Delhi
2	2	Siva	9283746565   Madurai	 Chennai

Left Outer Join:

Left outer join returns all the values in the first table and the matching values in the second table with Null values in the nonmatching column.


Query:

select a.Id, b.Sno, a.Name, a.PhoneNo, b.ResidentAddress, b.OfficeAddress 
from Detail a left outer join Address b on a.Id = b.Sno


Output:

Id	Sno	Name	PhoneNo	       Resident Office
1	1	Ramesh	9786756453	Trichy	Delhi
2	2	Siva	9283746565	Madurai	Chennai
3	NULL	Kamal	9192939495	NULL	NULL

Right Outer Join:

Right outer join returns all the values in the second table and the matching values in the first table with Null values in the nonmatching column.


Query:

select a.Id, b.Sno, a.Name, a.PhoneNo, b.ResidentAddress, b.OfficeAddress 
from Detail a right outer join Address b on a.Id = b.Sno


Output:

Id	Sno	Name	PhoneNo	       Resident	Office
1	1	Ramesh	9786756453	Trichy	        Delhi
2	2	Siva	9283746565	Madurai	        Chennai
NULL	4	NULL	NULL		Coimbatore	Banglore

Full Outer Join:

Full outer join returns all the value in both the table and NULL value in nonmatching column of both the tables.


Query:

select a.Id, b.Sno, a.Name, a.PhoneNo, b.ResidentAddress, b.OfficeAddress 
from Detail a full outer join Address b on a.Id = b.Sno


Output:

Id	Sno	Name	PhoneNo	       Resident	Office
1	1	Ramesh	9786756453	Trichy	        Delhi
2	2	Siva	9283746565	Madurai	        Chennai
3	NULL	Kamal	9192939495	NULL		NULL
NULL	4	NULL	NULL		Coimbatore	Banglore

Cross Join:

Cross join gives the cartesian product of both the tables.


Query:

select a.Id, b.Sno, a.Name, a.PhoneNo, b.ResidentAddress, b.OfficeAddress 
from Detail a cross join Address b 


Output:

Id	Sno	Name	PhoneNo	       Resident	Office
1	1	Ramesh	9786756453	Trichy	 	Delhi
2	1	Siva	9283746565	Trichy		Delhi
3	1	Kamal	9192939495	Trichy		Delhi
1	2	Ramesh	9786756453	Madurai	        Chennai
2	2	Siva	9283746565	Madurai	        Chennai
3	2	Kamal	9192939495	Madurai	        Chennai
1	4	Ramesh	9786756453	Coimbatore	Banglore
2	4	Siva	9283746565	Coimbatore	Banglore
3	4	Kamal	9192939495	Coimbatore	Banglore

Self Join:

Self join is the process of joining the table with itself. To compare two columns of same table we can use self join with different alias name.


Query:

select a.* from Detail a join Detail b on a.Id=b.Id


Output:

Id	Name	PhoneNo
1	Ramesh	9786756453
2	Siva	9283746565
3	Kamal	9192939495

Comments

Author: vinodh17 Aug 2011 Member Level: Silver Points : 1

Hi,

Thanks for this Nice post!

How to use multiple table in Inner Join ?

Thanks and Regards

Vinodh

Author: kirthiga17 Aug 2011 Member Level: Gold Points : 1

Hai vinodh,

Using multiple tables in join is similar as that of using on two tables. Consider this example query


select a.Id, b.Sno, a.Name, a.PhoneNo, b.ResidentAddress, b.OfficeAddress, c.*
from Detail a inner join Address b on a.Id = b.Sno
   inner join Summary c on a.Name = c.Name

By using this query we find that the name column of Detail table join with name column of Summary table.

Author: SivaSaiKrishna26 Aug 2011 Member Level: Silver Points : 0

Hi,

Nice Post.

Thanks & Regards
SivaSaiKrishna

Author: Suresh12 Sep 2011 Member Level: Gold Points : 1

Hi kirthiga,

It is Excellent Article about Types of Joins in SQL Server.

Good Explanation. Keep it up.

Regards
S.Suresh

Author: Jivani08 May 2013 Member Level: Gold Points : 0

you can join information are about of join

and use of where in best of example please folow link chech

http://bharat1990.wordpress.com/2013/04/25/types-of-join-in-sql-server/

Author: Ultimaterengan17 May 2013 Member Level: Gold Points : 0

Nice article .......keep contribute .

Author: Mohite Mina20 Mar 2014 Member Level: Silver Points : 10

Types of Joins

1) Inner Joins
2) Outer Joins
3) Self Join

Inner Join
The join that displays only the rows that have a match in both the joined tables is known as inner join. This is default join in the query and view Designer.
SELECT *
FROM tblProgram1 TPA
INNER JOIN tblProgram2 TPB
ON TPA.id = TPB.id
We can write our inner join query like this also it will give same result
SELECT *
FROM tblProgram1 TPA
JOIN tblProgram2 TPB
ON TPA.id = TPB.id
In inner Join we are having different types of Joins those are

1) Equi Join
2) Natural Join
3) Cross Join
Equi Join
An equi-join is used to match two columns from two tables using explicit operator =:
select *
from tblProgram1 TPA, tblProgram2 TPB
where TPA.id = TPB.id

Natural Joins
The Natural join is same as our Equi join but only the difference is it will restrict
to display redundant values.
SELECT *
FROM tblProgram1
NATURAL JOIN tblProgram2
These NATURAL Joins won't work in our SQL Server (only supports in Oracle) it will throw
syntax error.
Cross Join
A cross join that produces Cartesian product of the tables that involved in the join. The
size of a Cartesian product is the number of the rows in first table multiplied by the
number of rows in the second table.
SELECT *
FROM tblProgram1
CROSS JOIN tblProgram2
Or we can write it in another way also
select *
from tblProgram1 , tblProgram2

Outer Joins

A join that return all the rows that satisfy the condition and unmatched rows in the joined table is an Outer Join.

We are having three types of Outer Joins

Left Outer Join
Right Outer Join
Full Outer Join

Left Outer Join

The left outer join displays all the rows from the first table and matched rows from the second table.

SELECT u.id,u.Program,o.chapter
FROM tblProgram1 u
LEFT OUTER JOIN tblProgram2 o
ON u.id=o.id

Right Outer Join

The right outer join displays all the rows from the second table and matched rows from the first table.

SELECT TPA.id,TPA.Program,TPB.chapter
FROM tblProgram1 TPA
RIGHT OUTER JOIN tblProgram2 TPB
ON TPA.id = TPB.id

Full Outer Join

Full Outer Join displays all the matching and non matching rows of both the tables.

SELECT TPA.id,TPA.Program,TPB.chapter
FROM tblProgram1 TPA
FULL OUTER JOIN tblProgram2 TPB
ON TPA.id = TPB.id

Self Join

Joining the table itself called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. Here we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.

select e2.EmpName,e1.EmpName as 'Manager'
from EmployeeDetails e1
INNER JOIN EmployeeDetails e2
on e1.EmpID=e2.EmpMgrID

Submit Article

Return to Article Index

Types of joins in sql server

Detail Table:

Address Table:

Inner Join:

Query:

Output:

Left Outer Join:

Query:

Output:

Right Outer Join:

Query:

Output:

Full Outer Join:

Query:

Output:

Cross Join:

Query:

Output:

Self Join:

Query:

Output:

Comments

About dotnetspider.com

Quick Links

General

Services