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



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: