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
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
----- get the data
from the Department
SELECT *
FROM Department
GO
|
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
---- 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
|
See demo at Our YouTube Channel -
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
|
See demo at Our YouTube Channel -
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
|
See demo at Our YouTube Channel -
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
|
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
|
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:
- Inner Join
- Left Outer Join
- 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
|
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
|
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
|
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
|
In
the above output, each record is coming from the right table as well as showing
the matching records also.
Here, it is noticeable that the join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal to symbol.
There are different types of joins available in SQL −
Here, it is noticeable that the join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal to symbol.
There are different types of joins available in SQL −
- INNER JOIN − returns rows when there is a match in both tables.
- LEFT JOIN − returns all rows from the left table, even if there are no matches in the right table.
- RIGHT JOIN − returns all rows from the right table, even if there are no matches in the left table.
- FULL JOIN − returns rows when there is a match in one of the tables.
- SELF JOIN − is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
No comments:
Post a Comment