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]
Rate This
[Total: 0    Average: 0/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has good experience in windows and web application development using ASP.Net and C#.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.