Joins in sqlserver

Posted Date: 17 Jun 2010 |Updated: 17-Jun-2010 |Category: SQL Server |Author: Narayana |Member Level: Gold |Points: 6 |

Types of Joins in Sqlserver

There are 3 Types of Joins used in sqlserver:

1.Inner Join

1.1 Equi join
1.2 Non Equi join
1.3 Self

2.Outer Join

2.1.Left Outer
2.2.Right Outer
2.3.Full Outer

3.Cross Join

Inner Join:
----------
This join returns rows when there is atleast one match in both the tables.

example: select * from table1 t1 INNERJOIN table2 t2 ON t1.col1=t2.col1

Outer Join:
-----------
It includes 3 different Joins

1.LeftOuterJoin:
This join retruns all the rows from the left table in conjuction with the matching rows from the right table. If there are are no columns matching in the right table, it returns NULL values.

example: select * from table1 t1 LEFTOUTERJOIN table2 t2 ON t1.col1=t2.col1

2.RightOuterJoin :

This join retruns all the rows from the right table in conjuction with the matching rows from the left table. If there are are no columns matching in the left table, it returns NULL values.

example: select * from table1 t1 RIGHTOUTERJOIN table2 t2 ON t1.col1=t2.col1

FullOuterJoin:

This join combines left outer join and right outer join. It returns rows from either table when the condtions are met and returns null value when there is no match.

Thanks & Regards
Narayana

Comments

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

you can join information get please check below link

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

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

Return to Article Index

Digital Marketing by SpiderWorks Technologies, Kochi - India. ©