Difference Between Inner Join And Left Join In SQL Server

Introduction

In this article, I am going to explain what is the difference between Inner join and Left join with examples. This is one of the very common SQL server interview questions.

Here we will be using SQL Server 2017 or you can use SQL Server 2008 or above.

Read my previous Joins in SQL Server 2017 part of this article using the below links before reading this article,

Definition of Joins

It is used to fetch/retrieve data from two or more related tables from data base. In general, tables are related to each other using foreign key constraints.

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 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 created.

Step 2 - Create 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 “tbl_Department” table.

select * from tbl_Department

output

Difference Between Inner Join And Left Join In SQL Server

Step 3 - Create 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 “tbl_Employee” table.

select * from tbl_Employee

Output

General Formula for Joins

SELECT ColumnList (whatever column you want to display)
FROM LeftTableName
JOIN_TYPE RightTableName
ON JoinCondition

INNER JOIN

Inner join returns only the matching rows between both the 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.

INNER JOIN Query

SELECT Name, Gender,country, Salary, DeptName
FROM tbl_Employee
INNER JOIN tbl_Department
ON tbl_Employee.DepartmentId = tbl_Department.DeptId

SELECT Name, Gender,country, Salary, DeptName
FROM tbl_Employee
JOIN tbl_Department
ON tbl_Employee.DepartmentId = tbl_Department.DeptId

Note

JOIN or INNER JOIN are the same. It's always better to use INNER JOIN.

select emp.Name,emp.Gender,emp.country,emp.Salary,dept.DeptName
from tbl_Employee emp
inner join tbl_Department dept
on emp.DepartmentId=dept.DeptId

Output

LEFT JOIN or LEFT OUTER JOIN

Left Join or Left Outer Join returns only the matching rows between both the tables, plus non matching rows from the left table.

Example

Write a query to retrieve Name, Gender, Country, Salary and DeptName from tbl_Employee and tbl_Department table.

LEFT JOIN or LEFT OUTER JOINQuery

SELECT Name, Gender,country, Salary, DeptName
FROM tbl_Employee
LEFT OUTER JOIN tbl_Department
ON tbl_Employee.DepartmentId = tbl_Department.DeptId

SELECT Name, Gender,country, Salary, DeptName
FROM tbl_Employee
LEFT JOIN tbl_Department
ON tbl_Employee.DepartmentId = tbl_Department.DeptId

Note

You can use, LEFT JOIN or LEFT OUTER JOIN. OUTER keyword is optional.

select emp.Name,emp.Gender,emp.country,emp.Salary,dept.DeptName
from tbl_Employee emp
LEFT JOIN tbl_Department dept
on emp.DepartmentId=dept.DeptId

OutPut

What is the difference between INNER JOIN and LEFT JOIN

INNER JOIN

Inner join returns only the matching rows between both the tables, non matching rows are eliminated.

LEFT JOIN

Left Join or Left Outer Join returns only the matching rows between both the tables, plus non matching rows from the left table.

What is the Difference between INNER JOIN and JOIN

There is no difference between inner join and join, they are exactly the same.

LEFT JOIN and LEFT OUTER JOIN
RIGHT JOIN and RIGHT OUTER JOIN
FULL JOIN and FULL OUTER JOIN

Conclusion

In this article, I explained the difference between inner join and left join in SQL Server with examples. I hope this article has helped you to understanding this topic. Post your valuable feedback in the comments section.