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)