SQL Joins

SQL JOIN combines records/rows from two or more tables based on a related column between them.


Table: Department
SELECT * FROM Department WITH (NOLOCK)

DepartmentID
DepartmentName
IsActive
1
Computer Science
1
2
Maths
1
3
Chemistry
1
4
Physics
1

Table: Employee
SELECT * FROM Employee  WITH (NOLOCK)

EmployeeID
DepartmentID
EmployeeName
Gender
BirthDate
Salary
Detail
1
1
Krishnan
M
4/4/1974
30000
Krishnan Details
2
2
Raman
M
12/31/1974
29000
Raman Details
3
3
Durga
F
10/2/1980
25000
Durga Details
4
3
Saratha
F
9/2/1978
26000
Saratha Details
5
2
Shivan
M
11/6/1970
45000
Shivan Details
6
3
Parvathi
F
9/18/1975
35000
Parvathi Details
7
0
Easwari
F
10/12/1990
10000
Parvathi Details
8
0
Donald
M
11/11/1990
12000
Donald Details

By looking the above sample table data, we can say

The EmployeeID 7 and 8 do not have the DepartmentID (not associated with department).
The DepartmentID 4 does not have Employees.


The different types of JOINs are;

·         INNER JOIN – Select records that have matching values in both (LEFT and RIGHT) tables.




-- EXAMPLE QUERY FOR INNER JOIN

SELECT e.EmployeeID,
       e.EmployeeName,
       d.DepartmentID,
       d.DepartmentName, 
       CASE WHEN e.Gender = 'F' THEN 'Female'
            WHEN e.Gender = 'M' THEN 'Male' ELSE NULL
       END AS Gender,
       e.Salary     
FROM Employee e WITH (NOLOCK)
INNER JOIN Department d WITH (NOLOCK) ON d.DepartmentID = e.DepartmentID

 RESULT: Returns only matched records between Employee(LEFT) and Department(RIGHT) tables.

EmployeeID
EmployeeName
DepartmentID
DepartmentName
Gender
Salary
1
Krishnan
1
Computer Science
Male
30000
2
Raman
2
Maths
Male
29000
3
Durga
3
Chemistry
Female
25000
4
Saratha
3
Chemistry
Female
26000
5
Shivan
2
Maths
Male
45000
6
Parvathi
3
Chemistry
Female
35000


·         LEFT (OUTER) JOIN – Select all records from the LEFT table and matched records from RIGHT table.



-- EXAMPLE QUERY FOR LEFT JOIN

SELECT e.EmployeeID,
       e.EmployeeName,
       d.DepartmentID,
       d.DepartmentName, 
       CASE WHEN e.Gender = 'F' THEN 'Female'
            WHEN e.Gender = 'M' THEN 'Male' ELSE NULL
       END AS Gender,
       e.Salary     
FROM Employee e WITH (NOLOCK)
LEFT JOIN Department d WITH (NOLOCK) ON d.DepartmentID = e.DepartmentID

RESULT: Returns all the Employee(LEFT) table records, including not matched with department(RIGHT) table.

EmployeeID
EmployeeName
DepartmentID
DepartmentName
Gender
Salary
1
Krishnan
1
Computer Science
Male
30000
2
Raman
2
Maths
Male
29000
3
Durga
3
Chemistry
Female
25000
4
Saratha
3
Chemistry
Female
26000
5
Shivan
2
Maths
Male
45000
6
Parvathi
3
Chemistry
Female
35000
7
Easwari
NULL
NULL
Female
10000
8
Donald
NULL
NULL
Male
12000

·         RIGHT (OUTER) JOIN – Select all records from the RIGHT table and matched records from LEFT table.


-- EXAMPLE QUERY FOR RIGHT JOIN

SELECT e.EmployeeID,
       e.EmployeeName,
       d.DepartmentID,
       d.DepartmentName,   
       CASE WHEN e.Gender = 'F' THEN 'Female'
            WHEN e.Gender = 'M' THEN 'Male' ELSE NULL
       END AS Gender,
       e.Salary     
FROM Employee e WITH (NOLOCK)
RIGHT JOIN Department d WITH (NOLOCK) ON d.DepartmentID = e.DepartmentID

RESULT: Returns all the Department(RIGHT) table records, including not matched with Employee(LEFT) table.

EmployeeID
EmployeeName
DepartmentID
DepartmentName
Gender
Salary
1
Krishnan
1
Computer Science
Male
30000
2
Raman
2
Maths
Male
29000
5
Shivan
2
Maths
Male
45000
3
Durga
3
Chemistry
Female
25000
4
Saratha
3
Chemistry
Female
26000
6
Parvathi
3
Chemistry
Female
35000
NULL
NULL
4
Physics
NULL
NULL


·         FULL (OUTER) JOIN – Select all records when there is a match in either LEFT or RIGHT table records.



-- EXAMPLE QUERY FOR FULL OUTER

SELECT e.EmployeeID,
       e.EmployeeName,
       d.DepartmentID,
       d.DepartmentName, 
       CASE WHEN e.Gender = 'F' THEN 'Female'
            WHEN e.Gender = 'M' THEN 'Male' ELSE NULL
       END AS Gender,
       e.Salary     
FROM Employee e WITH (NOLOCK)
FULL OUTER JOIN Department d WITH (NOLOCK)  ON d.DepartmentID = e.DepartmentID

RESULT: Returns all the Employee(LEFT) and Department(RIGHT) tables record there is match in either Employee OR Department table records.

EmployeeID
EmployeeName
DepartmentID
DepartmentName
Gender
Salary
1
Krishnan
1
Computer Science
Male
30000
2
Raman
2
Maths
Male
29000
3
Durga
3
Chemistry
Female
25000
4
Saratha
3
Chemistry
Female
26000
5
Shivan
2
Maths
Male
45000
6
Parvathi
3
Chemistry
Female
35000
7
Easwari
NULL
NULL
Female
10000
8
Donald
NULL
NULL
Male
12000
NULL
NULL
4
Physics
NULL
NULL

·         CROSS JOIN OR (Cartesian Product) – Return a table which consists of records which combines each record from the LEFT table with each record of the RIGHT table.


       
-- EXAMPLE QUERY FOR CROSS JOIN

SELECT e.EmployeeID,
       e.EmployeeName,
       d.DepartmentID,
       d.DepartmentName, 
       CASE WHEN e.Gender = 'F' THEN 'Female'
            WHEN e.Gender = 'M' THEN 'Male' ELSE NULL
       END AS Gender,
       e.Salary     
FROM Employee e WITH (NOLOCK)
CROSS JOIN Department d WITH (NOLOCK)
 RESULT: Returns each record from the Employee(LEFT) table with each record of the Department(RIGHT) table.


EmployeeID
EmployeeName
DepartmentID
DepartmentName
Gender
Salary
1
Krishnan
1
Computer Science
Male
30000
2
Raman
1
Computer Science
Male
29000
3
Durga
1
Computer Science
Female
25000
4
Saratha
1
Computer Science
Female
26000
5
Shivan
1
Computer Science
Male
45000
6
Parvathi
1
Computer Science
Female
35000
7
Easwari
1
Computer Science
Female
10000
8
Donald
1
Computer Science
Male
12000
1
Krishnan
2
Maths
Male
30000
2
Raman
2
Maths
Male
29000
3
Durga
2
Maths
Female
25000
4
Saratha
2
Maths
Female
26000
5
Shivan
2
Maths
Male
45000
6
Parvathi
2
Maths
Female
35000
7
Easwari
2
Maths
Female
10000
8
Donald
2
Maths
Male
12000
1
Krishnan
3
Chemistry
Male
30000
2
Raman
3
Chemistry
Male
29000
3
Durga
3
Chemistry
Female
25000
4
Saratha
3
Chemistry
Female
26000
5
Shivan
3
Chemistry
Male
45000
6
Parvathi
3
Chemistry
Female
35000
7
Easwari
3
Chemistry
Female
10000
8
Donald
3
Chemistry
Male
12000
1
Krishnan
4
Physics
Male
30000
2
Raman
4
Physics
Male
29000
3
Durga
4
Physics
Female
25000
4
Saratha
4
Physics
Female
26000
5
Shivan
4
Physics
Male
45000
6
Parvathi
4
Physics
Female
35000
7
Easwari
4
Physics
Female
10000
8
Donald
4
Physics
Male
12000


-- SQL QUERY EXAMPLE

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))
DROP TABLE [dbo].[Employee]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]') AND type in (N'U'))
DROP TABLE [dbo].[Department]
GO

CREATE TABLE [dbo].[Department](
       [DepartmentID] [int] NOT NULL IDENTITY(0,1),
       [DepartmentName] [varchar](50) NOT NULL,
       [IsActive] [bit] NOT NULL CONSTRAINT DF_Department_IsActive DEFAULT (1)
 CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED
(
       [DepartmentID] ASC
)
 ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Employee](
       [EmployeeID] int NOT NULL IDENTITY(1,1),
       [DepartmentID] int NOT NULL, --CONSTRAINT FK_Employee_DepartmentID REFERENCES [Department](DepartmentID),
       [EmployeeName] varchar(50) NOT NULL,
       [Gender] char(1) NOT NULL CONSTRAINT CH_Employee_Gender CHECK ([Gender] in('M','F','O')),
       [BirthDate] date NOT NULL,
       [Salary] decimal(10,2) NULL,
       [Detail] varchar(max) NULL,
       [IsActive] bit NOT NULL CONSTRAINT DF_Employee_IsActive DEFAULT (1),
 CONSTRAINT [UQ_Employee_EmployeeName] UNIQUE(EmployeeName),
 CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
(
       [EmployeeID] ASC
)
ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


SET IDENTITY_INSERT [dbo].[Department] ON
INSERT [dbo].[Department] ([DepartmentID], [DepartmentName], [IsActive]) VALUES (1, N'Computer Science', 1)
INSERT [dbo].[Department] ([DepartmentID], [DepartmentName], [IsActive]) VALUES (2, N'Maths', 1)
INSERT [dbo].[Department] ([DepartmentID], [DepartmentName], [IsActive]) VALUES (3, N'Chemistry', 1)
INSERT [dbo].[Department] ([DepartmentID], [DepartmentName], [IsActive]) VALUES (4, N'Physics', 1)
SET IDENTITY_INSERT [dbo].[Department] OFF


SET IDENTITY_INSERT [dbo].[Employee] ON
INSERT [dbo].[Employee] ([EmployeeID], [DepartmentID], [EmployeeName], [Gender], [BirthDate], [Salary], [Detail], [IsActive])
VALUES (1, 1, N'Krishnan', N'M', '04/04/1974 12:00PM', CAST(30000.00 AS Decimal(10, 2)), N'Krishnan Details', 1)
INSERT [dbo].[Employee] ([EmployeeID], [DepartmentID], [EmployeeName], [Gender], [BirthDate], [Salary], [Detail], [IsActive])
VALUES (2, 2, N'Raman', N'M', '12/31/1974', CAST(29000.00 AS Decimal(10, 2)), N'Raman Details', 1)
INSERT [dbo].[Employee] ([EmployeeID], [DepartmentID], [EmployeeName], [Gender], [BirthDate], [Salary], [Detail], [IsActive])
VALUES (3, 3, N'Durga', N'F', '10/02/1980', CAST(25000.00 AS Decimal(10, 2)), N'Durga Details', 1)
INSERT [dbo].[Employee] ([EmployeeID], [DepartmentID], [EmployeeName], [Gender], [BirthDate], [Salary], [Detail], [IsActive])
VALUES (4, 3, N'Saratha', N'F', '09/02/1978', CAST(26000.00 AS Decimal(10, 2)), N'Saratha Details', 1)
INSERT [dbo].[Employee] ([EmployeeID], [DepartmentID], [EmployeeName], [Gender], [BirthDate], [Salary], [Detail], [IsActive])
VALUES (5, 2, N'Shivan', N'M', '11/06/1970', CAST(45000.00 AS Decimal(10, 2)), N'Shivan Details', 1)
INSERT [dbo].[Employee] ([EmployeeID], [DepartmentID], [EmployeeName], [Gender], [BirthDate], [Salary], [Detail], [IsActive])
VALUES (6, 3, N'Parvathi', N'F', '9/18/1975', CAST(35000.00 AS Decimal(10, 2)), N'Parvathi Details', 1)
INSERT [dbo].[Employee] ([EmployeeID], [DepartmentID], [EmployeeName], [Gender], [BirthDate], [Salary], [Detail], [IsActive])
VALUES (7, 0, N'Easwari', N'F', '10/12/1990', 10000.00, N'Parvathi Details', 1)
INSERT [dbo].[Employee] ([EmployeeID], [DepartmentID], [EmployeeName], [Gender], [BirthDate], [Salary], [Detail], [IsActive])
VALUES (8, 0, N'Donald', N'M', '11/11/1990', 12000.00, N'Donald Details', 1)
SET IDENTITY_INSERT [dbo].[Employee] OFF

GO

SELECT * FROM Department WITH (NOLOCK)
GO

SELECT * FROM Employee  WITH (NOLOCK)
GO

-- EXAMPLE QUERY FOR INNER JOIN

SELECT e.EmployeeID,
       e.EmployeeName,
       d.DepartmentID,
       d.DepartmentName, 
       CASE WHEN e.Gender = 'F' THEN 'Female'
            WHEN e.Gender = 'M' THEN 'Male' ELSE NULL
       END AS Gender,
       e.Salary     
FROM Employee e WITH (NOLOCK)
INNER JOIN Department d WITH (NOLOCK) ON d.DepartmentID = e.DepartmentID


-- EXAMPLE QUERY FOR LEFT JOIN

SELECT e.EmployeeID,
       e.EmployeeName,
       d.DepartmentID,
       d.DepartmentName, 
       CASE WHEN e.Gender = 'F' THEN 'Female'
            WHEN e.Gender = 'M' THEN 'Male' ELSE NULL
       END AS Gender,
       e.Salary     
FROM Employee e WITH (NOLOCK)
LEFT JOIN Department d WITH (NOLOCK) ON d.DepartmentID = e.DepartmentID

-- EXAMPLE QUERY FOR RIGHT JOIN

SELECT e.EmployeeID,
       e.EmployeeName,
       d.DepartmentID,
       d.DepartmentName,   
       CASE WHEN e.Gender = 'F' THEN 'Female'
            WHEN e.Gender = 'M' THEN 'Male' ELSE NULL
       END AS Gender,
       e.Salary     
FROM Employee e WITH (NOLOCK)
RIGHT JOIN Department d WITH (NOLOCK) ON d.DepartmentID = e.DepartmentID


-- EXAMPLE QUERY FOR FULL OUTER

SELECT e.EmployeeID,
       e.EmployeeName,
       d.DepartmentID,
       d.DepartmentName, 
       CASE WHEN e.Gender = 'F' THEN 'Female'
            WHEN e.Gender = 'M' THEN 'Male' ELSE NULL
       END AS Gender,
       e.Salary     
FROM Employee e WITH (NOLOCK)
FULL OUTER JOIN Department d WITH (NOLOCK)  ON d.DepartmentID = e.DepartmentID

-- EXAMPLE QUERY FOR CROSS JOIN

SELECT e.EmployeeID,
       e.EmployeeName,
       d.DepartmentID,
       d.DepartmentName, 
       CASE WHEN e.Gender = 'F' THEN 'Female'
            WHEN e.Gender = 'M' THEN 'Male' ELSE NULL
       END AS Gender,
       e.Salary     
FROM Employee e WITH (NOLOCK)

CROSS JOIN Department d WITH (NOLOCK)

No comments:

Post a Comment