Joins in SQL Server

JOINS:

Joins are used to retrieve the data from different tables by combining columns. By comparing common fields in all tables, data will be retrieved.

There are different joins available in SQL server.

They are:

1)   Join/Inner join

2)   LEFT [Outer] Join

3)   RIGHT [Outer] Join

4)   FULL Join

5)   SELF Join

6)   CROSS JOIN

Let us create two tables as below, and observe the out put of different joins.

CREATE TABLE tbl_Dept(

 DeptID INT PRIMARY KEY IDENTITY(1,1),

 Department VARCHAR(50)

 )

CREATE TABLE tbl_Employees(

 EmpID INT PRIMARY KEY IDENTITY(1,1),

 EmpName VARCHAR(50),

 DeptID INT

 ) 

INNER JOIN/JOIN

INNER JOIN is used to retrieve data from two or more tables by comparing common fields.

In INNER JOIN, only the exactly matching rows will be returned.

Syntax:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

Example:

SELECT E.EmpID,E.EmpName,D.Department FROM tbl_Employees E

INNER JOIN tbl_Dept D ON

E.DeptID = D.DeptID

In the above query DeptID is the common field in both tables.

  

LEFT JOIN/LEFT OUTER JOIN

LEFT JOIN is used to retrieve data from two or more tables by comparing common fields.

LEFT JOIN and LEFT OUTER JOIN both are same in functionality.

LEFT JOIN Query returns all rows from LEFT tables and matching rows only from RIGHT table.

Not matching rows from RIGHT table will return with NULL value.

Syntax:

SELECT column_name(s)
FROM table1
LEFT [Outer] JOIN table2
ON table1.column_name=table2.column_name;

Example:

SELECT E.EmpID,E.EmpName,D.Department FROM tbl_Employees E

LEFT JOIN tbl_Dept D ON

E.DeptID = D.DeptID

RIGHT JOIN/RIGHT OUTER JOIN

RIGHT JOIN is used to retrieve data from two or more tables by comparing common fields.

RIGHT JOIN and RIGHT OUTER JOIN both are same in functionality.

RIGHT JOIN Query returns all rows from RIGHT tables and matching rows only from LEFT table.

Not matching rows from LEFT table will return with NULL value.

Syntax:

SELECT column_name(s)
FROM table1
RIGHT [Outer] JOIN table2
ON table1.column_name=table2.column_name;

Example:

SELECT E.EmpID,E.EmpName,D.Department FROM tbl_Employees E

RIGHT JOIN tbl_Dept D ON

E.DeptID = D.DeptID

FULL  JOIN

FULL JOIN is used to retrieve all data from two or more tables by comparing common fields.

FULL JOIN Query returns all rows from LEFT and RIGHT tables.

Not matching rows from LEFT and RIGHT table will be return with NULL value.

Syntax:

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name=table2.column_name;

Example:

SELECT E.EmpID,E.EmpName,E.DeptID, D.DeptID, D.Department FROM tbl_Employees E

FULL JOIN tbl_Dept D ON

E.DeptID = D.DeptID

  

SELF JOIN

SELF JOIN is used to compare the table itself.

There is no any key word “SELF” exists in SQL server.

LEFT/RIGHT/FULL keywords we can use for self join query.

Syntax:

SELECT column_name(s)
FROM table1 t1
JOIN table1 t2
ON t1.column_name=t2.column_name;

Example:

SELECT E.EmpID, E.EmpName, E.DeptID, E1.EmpName FROM tbl_Employees E  

[LEFT/RIGHT/INNER]JOIN tbl_Employees E1

ON E1.EmpID=E.ManagerID

CROSS JOIN

Cross join query returns all records where each row from table1 combines with all rows from table2.

CROSS JOIN can be called in two ways using JOINS and tables separated by comma (,).

Syntax:

SELECT * OR column_name(s)
FROM table1 t1
CROSS JOIN table1 t2

(OR)

SELECT * or column_name(s)
FROM table1 t1, table1 t2

Example:

SELECT * FROM tbl_Employees E  

CROSS JOIN tbl_Employees E1

CROSS TAB Query:

Cross tab query is used for reporting purpose. Using cross tab query we can convert, row values into column names.

SELECT Yearc,Jan,FEB,MARCH,APRIL,May FROM tbl_Revenue

PIVOT

(

SUM(Revenue)

for Monthc in(Jan,FEB,MARCH,APRIL,May)

)

PIV