Tuesday, June 23, 2015

SQL - Introduction of Joins

Joins are used to link one and more than two tables together to get the information from the database.  Join is the keyword which is used in SQL queries for joining two or more tables. This is the most beautiful concept of SQL.
Joins are basically worked on the database normalization where data is distributed into tables with primary key and foreign key relationship concepts.

Types of join – SQL support the following join methods –
              1)      Inner Join
              2)     Outer Join
                     a.      Left Outer Join
                     b.      Right Outer Join
                     c.       Full Outer Join
              3)     Cross Join
              4)   Self Join

To explain each the above join methods, we are taken employee master and department master tables as given below-
Use tempdb
go

----- Create a Employee Master Table
CREATE TABLE EMP_MASTER
(
EMPID INT, EMP_Name Varchar(30), DeptId Int
)

----- Create a department master
CREATE Table Department
(DeptId Int, DepartmentName varchar(30))
Go

---- Insert data into Emp_Master Table
INSERT INTO EMP_MASTER Values
(1, 'Ryan Arjun', 1),
(2, 'Bill Smith', NULL),
(3, 'Lucy Gary', 2), 
(4, 'Ken McHenry', NULL),
(5, 'Clinton Johnson', 3),
(6, 'Kimmy Wang', NULL)

---- Insert data into Emp_Master Table
INSERT INTO Department Values
(1, 'Finance'),
(2, 'Marketing'),
(3, 'Admin'),
(4, 'IT')
GO

----- get the data from the Employee Master
SELECT * FROM EMP_MASTER
GO
EMPID
EMP_Name
DeptId
1
Ryan Arjun
1
2
Bill Smith
NULL
3
Lucy Gary
2
4
Ken McHenry
NULL
5
Clinton Johnson
3
6
Kimmy Wang
NULL

----- get the data from the Department
SELECT * FROM Department
GO
DeptId
Department Name
1
Finance
2
Marketing
3
Admin
4
IT


Inner Join – This is the default nature of the join between two tables. If you are not providing the join type as above then inner join will be applied by default.   
This join is the responsible to fetch the matching records between two or more tables. This join will return only matching records as given below:
Use tempdb
go

---- use of inner join
SELECT
E.EMPID,
E.EMP_Name [EMP Name],
D.DepartmentName  [Department]
FROM EMP_MASTER E
INNER JOIN Department D
ON E.DeptId=D.DeptId
GO

EMPID
EMP Name
Department
1
Ryan Arjun
Finance
3
Lucy Gary
Marketing
5
Clinton Johnson
Admin

---- use of natural behavior of two table join
SELECT
E.EMPID,
E.EMP_Name [EMP Name],
D.DepartmentName  [Department]
FROM EMP_MASTER E ,   Department D
where E.DeptId=D.DeptId
GO
EMPID
EMP Name
Department
1
Ryan Arjun
Finance
3
Lucy Gary
Marketing
5
Clinton Johnson
Admin


Left Outer Join - The left outer join will return all the rows of the left table as well as matching rows of the two tables. If there are no columns matching in the right table, it returns NULL values as given below:
Use tempdb
go

---- use of left outer join
SELECT
E.EMPID,
E.EMP_Name [EMP Name],
D.DepartmentName  [Department]
FROM EMP_MASTER E  
LEFT OUTER JOIN Department D
ON E.DeptId=D.DeptId
GO

EMPID
EMP Name
Department
1
Ryan Arjun
Finance
2
Bill Smith
NULL
3
Lucy Gary
Marketing
4
Ken McHenry
NULL
5
Clinton Johnson
Admin
6
Kimmy Wang
NULL


Right Outer Join – This is the opposite of the left outer join. The right outer join will return all the rows of the right table as well as matching rows of the two tables. If there are no columns matching in the left table, it returns NULL values as given below:
Use tempdb
go

---- use of right outer join
SELECT
E.EMPID,
E.EMP_Name [EMP Name],
D.DepartmentName  [Department]
FROM EMP_MASTER E  
RIGHT OUTER JOIN Department D
ON E.DeptId=D.DeptId
GO

EMPID
EMP Name
Department
1
Ryan Arjun
Finance
3
Lucy Gary
Marketing
5
Clinton Johnson
Admin
NULL
NULL
IT


Full Outer Join – This join will return all rows from the both tables. We can say that this join combines left outer join and right outer join together. It returns row from either table when the conditions are met and returns null value when there is no match.
Use tempdb
go

---- use of Full outer join
SELECT
E.EMPID,
E.EMP_Name [EMP Name],
D.DepartmentName  [Department]
FROM EMP_MASTER E  
FULL OUTER JOIN Department D
ON E.DeptId=D.DeptId
GO

EMPID
EMP Name
Department
1
Ryan Arjun
Finance
2
Bill Smith
NULL
3
Lucy Gary
Marketing
4
Ken McHenry
NULL
5
Clinton Johnson
Admin
6
Kimmy Wang
NULL
NULL
NULL
IT


Cross Join – This join is a Cartesian join that does not necessitate any condition to join. The result set contains records that are multiplication of record number from both the tables as given below:
Use tempdb
go

---- use of cross join
SELECT *
FROM EMP_MASTER E  
CROSS JOIN Department D
where D.DeptId=2
GO

EMPID
EMP_Name
DeptId
DeptId
DepartmentName
1
Ryan Arjun
1
2
Marketing
2
Bill Smith
NULL
2
Marketing
3
Lucy Gary
2
2
Marketing
4
Ken McHenry
NULL
2
Marketing
5
Clinton Johnson
3
2
Marketing
6
Kimmy Wang
NULL
2
Marketing



Self Join – This is very simple join of SQL Server and depends on the single table. This is not a method but a simple case where a single table joins itself to get the information. SQL does not any keyword as Self Join. Self Join works by using anyone of the method is given below: 

  1. Inner Join
  2. Left Outer Join
  3. Right Outer Join
Example of Self Join –
Employee Master table could be a best example to understand the self join case:
Use tempdb
go

----- Create a Employee Master Table
CREATE TABLE EMP_MASTER
(
EMPID INT, EMP_Name Varchar(30), MangerId Int
)

Go

---- Insert data into Emp_Master Table
INSERT INTO EMP_MASTER Values
(1, 'Ryan Arjun', NULL),
(2, 'Bill Smith', 1),
(3, 'Lucy Gary', 1), 
(4, 'Ken McHenry', 2),
(5, 'Clinton Johnson', 2),
(6, 'Kimmy Wang', 3),
(7, 'David Ramsay', 3)
GO

----- get the data from the Employee Master
SELECT * FROM EMP_MASTER
GO
EMPID
EMP_Name
MangerId
1
Ryan Arjun
NULL
2
Bill Smith
1
3
Lucy Gary
1
4
Ken McHenry
2
5
Clinton Johnson
2
6
Kimmy Wang
3
7
David Ramsay
3


In this scenario we need to find out the reporting manager of the each employee. So we need to apply self join approach here means we need to use the same table to join itself.
Self Join with Inner Join method – In this query, we are getting the employee id, employee name with their manager as given below-
------ use of inner join will show only the matching records
SELECT E.EMPID,
E.EMP_Name AS [Employee Name],
M.EMP_Name as [Manager Name] 
FROM EMP_MASTER E
INNER JOIN
EMP_MASTER M ON E.MangerId=M.EMPID

EMPID
Employee Name
Manager Name
2
Bill Smith
Ryan Arjun
3
Lucy Gary
Ryan Arjun
4
Ken McHenry
Bill Smith
5
Clinton Johnson
Bill Smith
6
Kimmy Wang
Lucy Gary
7
David Ramsay
Lucy Gary


In the above output, one record is missing because of there is no manager for that employee in the table.
Self Join with left outer Join method – In this query, we are getting the employee id, employee name with their manager as given below-
------ use of inner join will show only the matching records

SELECT E.EMPID,
E.EMP_Name AS [Employee Name],
M.EMP_Name as [Manager Name] 
FROM EMP_MASTER E
LEFT OUTER JOIN
EMP_MASTER M ON E.MangerId=M.EMPID

EMPID
Employee Name
Manager Name
1
Ryan Arjun
NULL
2
Bill Smith
Ryan Arjun
3
Lucy Gary
Ryan Arjun
4
Ken McHenry
Bill Smith
5
Clinton Johnson
Bill Smith
6
Kimmy Wang
Lucy Gary
7
David Ramsay
Lucy Gary

In the above output, each record is coming from the left table.

Self Join with Right outer Join method – In this query, we are getting the employee id, employee name with their manager as given below-
------ use of inner join will show only the matching records

SELECT E.EMPID,
E.EMP_Name AS [Employee Name],
M.EMP_Name as [Manager Name] 
FROM EMP_MASTER E
RIGHT OUTER JOIN
EMP_MASTER M ON E.MangerId=M.EMPID

EMPID
Employee Name
Manager Name
2
Bill Smith
Ryan Arjun
3
Lucy Gary
Ryan Arjun
4
Ken McHenry
Bill Smith
5
Clinton Johnson
Bill Smith
6
Kimmy Wang
Lucy Gary
7
David Ramsay
Lucy Gary
NULL
NULL
Ken McHenry
NULL
NULL
Clinton Johnson


In the above output, each record is coming from the right table as well as showing the matching records also.

No comments:

Post a Comment

Popular Posts