Unfolding | Set Based Approach | How join works internally – Episode 4

In this video, we are going to understand how join works internally. If you have a good understanding of joins and how it works, you can skip this video and move to the next video in this series. If not, lets start understanding the joins. You can download the script used in this example from below the video.

Here is the example script used in this video.

--Create database
CREATE DATABASE SetBasedApproach
GO

--Start using the database
USE SetBasedApproach
GO

--Create employee table
CREATE TABLE dbo.Employee
(
[Emp ID] INT,
[Emp Name] VARCHAR(256)
)
GO

--Create department table
CREATE TABLE dbo.Department
(
[Emp ID] INT,
[Dept Name] VARCHAR(256)
)
GO

--Insert some values in employee table
INSERT INTO dbo.Employee ([Emp ID], [Emp Name])
VALUES(1, 'Adam'),
(2, 'Joy'),
(3, 'Roy'),
(4, 'Mac')
GO

--Insert some values in department table
INSERT INTO dbo.Department ([Emp ID], [Dept Name])
VALUES(1, 'Engineering'),
(2, 'Human Resources'),
(2, 'Admin'),
(3, 'Human Resources')
GO

--Select the records
SELECT * FROM dbo.Employee
SELECT * FROM dbo.Department

--Cross join goes here
SELECT * FROM dbo.Employee CROSS JOIN dbo.Department

--Inner join goes here
SELECT * FROM dbo.Employee INNER JOIN dbo.Department ON Employee.[Emp ID] = Department.[Emp ID]

--Left join goes here
SELECT * FROM dbo.Employee LEFT JOIN dbo.Department ON Employee.[Emp ID] = Department.[Emp ID]

--Insert some more values to understand right and full join
INSERT INTO dbo.Department VALUES(0, 'Not Assigned')

--Right join goes here
SELECT * FROM dbo.Employee RIGHT OUTER JOIN dbo.Department ON Employee.[Emp ID] = Department.[Emp ID]

--Full join goes here
SELECT * FROM dbo.Employee FULL OUTER JOIN dbo.Department ON Employee.[Emp ID] = Department.[Emp ID]