Introduction
In this article, I am going to explain about joins and types of joins with examples. Here we will be using SQL Server 2017 or you can use SQL Server 2008 or above.
Definition
Joins are used to fetch/retrieve data from two or more related tables from a database. In general, tables are related to each other using foreign key constraints.
Types of Joins
There are four types of joins in SQL Server.
- Inner Join
- Outer Join
- Cross Join
- Self Join
Again, Outer Joins are divided into three types.
- Left Join or Left Outer Join
- Right Join or Right Outer Join
- Full Join or Full Outer Join
Now, I am going to explain different types of joins with examples and the differences between them.
Prerequisites
SQL Server 2017 or you can use SQL Server 2008 or above version.
Now, first, we will create a Database and two tables to apply the joins for understanding.
Creating a Database and two tables
Step 1. Create a Database
Open your SQL Server and use the following script to create the “chittadb” Database.
CREATE DATABASE chittadb;
Now, select the script query then press F5 or click on Execute button to execute the above script.
You should see a message, “Command(s) completed successfully.” This means your new database has been created.
Step 2. Create the first table
Open your SQL Server and use the following script to create table “tbl_Department”.
CREATE TABLE tbl_Department
(
DeptId INT PRIMARY KEY NOT NULL IDENTITY(1,1),
DeptName NVARCHAR(50),
DeptHead NVARCHAR(50),
Location NVARCHAR(100)
)
Execute the above query to create “tbl_Department “.
You should see a message, “Command(s) completed successfully.”
Now, data is inserted into the table.
INSERT INTO tbl_Department VALUES ('IT', 'Chitta', 'Chennai');
INSERT INTO tbl_Department VALUES ('Payroll', 'Akhil', 'Odisha');
INSERT INTO tbl_Department VALUES ('HR', 'Ram', 'Pune');
INSERT INTO tbl_Department VALUES ('Timesheet', 'Kannan', 'Chennai');
Execute the above query, you should see a message, “Command(s) completed successfully.”
Now retrieve all data from the “tbl_Department” table.
SELECT * FROM tbl_Department;
output

Step 3. Create the second table
Open your SQL Server and use the following script to create table “tbl_Employee”.
CREATE TABLE tbl_Employee (
EmpID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
Name NVARCHAR(50),
Gender NVARCHAR(50),
Country NVARCHAR(20),
Salary INT,
DepartmentId INT FOREIGN KEY REFERENCES tbl_Department(DeptId)
)
Execute the above query to create “tbl_Employee “.
You should see a message, “Command(s) completed successfully.”
Now, data is inserted into the table.
INSERT INTO tbl_Employee VALUES ('Jitu', 'Male', 'India', 4000, 1);
INSERT INTO tbl_Employee VALUES ('Rani', 'Female', 'India', 5000, 3);
INSERT INTO tbl_Employee VALUES ('Rohit', 'Male', 'India', 5500, 1);
INSERT INTO tbl_Employee VALUES ('Dibas', 'Male', 'India', 6500, 2);
INSERT INTO tbl_Employee VALUES ('Gajendra', 'Male', 'India', 3800, 2);
INSERT INTO tbl_Employee VALUES ('Raja', 'Male', 'India', 9000, 1);
INSERT INTO tbl_Employee VALUES ('Jeni', 'Female', 'India', 5800, 3);
INSERT INTO tbl_Employee VALUES ('Chandin', 'Female', 'India', 8500, 1);
INSERT INTO tbl_Employee VALUES ('pintu', 'Male', 'India', 9500, NULL);
INSERT INTO tbl_Employee VALUES ('Subrat', 'Male', 'India', 9800, NULL);
Execute the above query, you should see a message, “Command(s) completed successfully.”
Now retrieve all data from the “tbl_Employee” table.
SELECT * FROM tbl_Employee;
output

General Formula for Joins
SELECT ColumnList (whatever column you want to display)
FROM LeftTableName
JOIN JOIN_TYPE RightTableName
ON JoinCondition
INNER JOIN
Inner join returns only the matching rows between both tables; non-matching rows are eliminated.
Example
Write a query to retrieve Name, Gender, Country, Salary, and DeptName from tbl_Employee and tbl_Department table.






Anand NavalePosted Sep 17, 2020, 5:06 AM
Good work sir...
Onkar SharmaPosted Jun 23, 2020, 2:08 PM
Very well explained, thank you...
Jignesh KumarPosted Jun 23, 2020, 7:42 AM
Good explanation!!! Thanks for sharing ..