Difference Between Inner Join ,Left Join and Full Join in Sql

In this post, we will discuss one of the very common interview questions: what is the difference between inner join and left join. If you’re new to joins, I would strongly encourage you to read our SQL Server join tutorial first.

• Sql Joins With Realtime Examples

Now, let’s understand the difference between inner join and left join using the below two tables: University and TblStudent.

Notice that University_Id is the common column between these two tables. If we have to join these two tables, we will be joining them on the University_Id column. The first step is to create these tables, and here is the SQL script.

Table SQL Script

/****** Object:  Table [dbo].[TblStudent]    Script Date: 09/07/2020 11:30:10 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblStudent](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [StudentName] [nvarchar](max) NOT NULL,
    [Gender] [nvarchar](50) NULL,
    [University_Id] [int] NULL,
 CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
)
GO
/****** Object:  Table [dbo].[TblUniversity]    Script Date: 09/07/2020 11:30:10 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblUniversity](
    [University_Id] [int] IDENTITY(1,1) NOT NULL,
    [UniversityName] [nvarchar](max) NULL,
 CONSTRAINT [PK_University] PRIMARY KEY CLUSTERED 
(
    [University_Id] ASC
)
)
GO
SET IDENTITY_INSERT [dbo].[TblStudent] ON 
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (1, 'Logan', 'Male', NULL)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (2, 'Evelyn', 'Female', 2)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (3, 'Emma', 'Female', 4)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (4, 'Michael', 'Male', 3)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (5, 'Amelia', 'Female', 6)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (6, 'Wyatt', 'Male', 5)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (7, 'Grayson', 'Male', 7)
GO
SET IDENTITY_INSERT [dbo].[TblStudent] OFF
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] ON 
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (1, 'Massachusetts Institute of Technology (MIT)')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (2, 'Stanford University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (3, 'Harvard University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (4, 'University of Oxford')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (5, 'Tsinghua University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (6, 'Columbia University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (7, 'Cornell University')
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] OFF
GO

Now, let’s say we want to retrieve Student Name from TblStudent table and University Name from TblUniversity table.

If we have to achieve that, we’ll have to join these two tables and to join two tables in SQL Server, there are different types of joins: inner join, left join, right join, full join, and cross join.

So let’s use an inner join.

We are joining TblStudent table with TblUniversity table and we need to specify the join condition. So we are going to use University_Id from both the tables to join them because that’s the common column between the tables.

What is inner join?

We want the Student name and University name, so let’s specify them in the select list.

SELECT StudentName, UniversityName
FROM TblStudent
INNER JOIN TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id

Now let’s execute this query and see the output that we notice that student name and University name and look at the number of rows that we have got.

We have got six rows. And if you look at the rows that we have in the TblStudent, notice that we have got 7 rows, but we only got 6 rows in the result.

Let’s understand the reason why. First of all, notice that we are using an inner join here. So when we use an inner join, only the matching rows between the tables involved in the join are returned in the result set, non-matching rows are eliminated.

So if you look at Logan's record here, does he have a University_Id assigned? No, he doesn’t. So he does not have any matching rows within the TblUniversity table. He doesn’t have matching rows. So obviously he will be eliminated from the result set.

What is left join?

Now, let’s understand what a left join is. A left join is going to return all the rows from the left table, including the non-matching ones. So unlike an inner join which only includes the matching rows, a left join includes all the rows from the left table, irrespective of whether they have matching rows in the right table or not.

So now if we were to change the join type from inner to left join and then execute this query, notice that we get Logan's record as well.

SELECT StudentName, UniversityName
FROM TblStudent
Left JOIN TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id

Now, since Logan does not have a matching record within TblUniversity, the University name will obviously be NULL.

So we got all the rows from the left table that’s left to join. That’s basically the difference: an INNER join is going to return all the matching rows, whereas a left join is going to return all the rows from the left table, irrespective of whether they have matching rows or not.

For non-matching rows, obviously, for the columns that belong to the right table, the null value will be displayed because there are no matching rows.

There could be several other questions on JOINs in the SQL server interview. For example, the interviewer could ask you what is the difference between an inner join and a right join?

What is the right join?

If you look at the data that we have within the TblUniversity table, notice that we have 7 records and the Massachusetts Institute of Technology (MIT) University does not have any student assigned, meaning it does not have any matching records.

SELECT StudentName, UniversityName
FROM TblStudent
Right JOIN TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id

If we simply change this left join to right join and then once we execute this query, look at the output that we are going to get. Now, we again got 7 rows.

Now, this time we did not get Logan's record. Why? Logan's record is present in the left table, which is the TblStudent table, and we are using a right join here. What is the right join going to do? The right join is going to retrieve all the rows from the right table, including the non-matching ones.

So from the right table, which is the TblUniversity table, we are going to get all records. Massachusetts Institute of Technology (MIT) University does not have any matching row within the TblStudent table. That's why the Studentname column will be null for that university. 

However, Logan's record is not retrieved. Instead, the Massachusetts Institute of Technology (MIT) record is retrieved because it is present in the right table.

So now you can see that all rows from the right table are retrieved, including the ones that do not have matching rows within the left table. That's what the right join does.

A right join retrieves all the rows from the right table, including the non-matching ones. Left join retrieves all the rows from the left table, including the non-matching ones. Meanwhile, an inner join retrieves only the matching rows between the tables involved in the join, and non-matching rows will be eliminated.

What is the full join?

Full join retrieves all the rows from both tables, irrespective of whether they have matching rows or not. If we were to change this join to a full join and then execute this query, notice that we get 8 records.

SELECT StudentName, UniversityName
FROM TblStudent
Full JOIN TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id

The first row represents data from the left table that does not have a corresponding match in the right table (TblUniversity). The last row shows data from the TblUniversity table (the right table) that lacks a corresponding entry in the left table. 

Therefore, a full join retrieves all rows from both tables, regardless of whether they have matching records. Understanding the basics of joins is essential for answering related interview questions. 

For instance, an interviewer might inquire about the differences between left join and right join, or between right join and full join, or between right join and left join.

Another interesting question-

The difference between INNER JOIN and JOIN, or between LEFT JOIN and LEFT OUTER JOIN, RIGHT JOIN and RIGHT OUTER JOIN, and FULL JOIN and FULL OUTER JOIN is negligible. 

In SQL, using either term produces the same result and executes in the same manner, both in terms of performance and query execution. Therefore, they can be used interchangeably. 

Thank you for reading. Have a great day.