Difference between Inner Join ,Left Join and Full Join-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 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 and right join, full join, cross join.
So let’s use inner join.

From TblStudent table doing an inner join 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 inner join here. So when we use inner join, only the matching rows between the tables involved in the join are returned in the ResultSet, non-matching rows are eliminated.

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

what is left join?

Now let’s understand what is left join, left join is going to return all the rows from the left table, including the non-matching ones. So inner join only the matching ones and left join all the rows from the left table, irrespective of whether they have matching rows or not in the right table.
so now if we were to change this joint type from inner to left join and then once we execute this query, notice that we get Logan 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, table University name will obviously be NULL.

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

And for non-matching rows, you know, obviously, for the columns that belong to the right table, the null value will be displayed because there is no matching ROWS.

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

What is the right join?

if you look at the data that we have got within 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.

 

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 look at this time we did not get Logan record why ? Logan record is present in the left table. That is the TblStudent table and we are using right Join here. What is the right join going do ? the right joint is going to retrieve all the rows from the right table, including the non-matching ones.

So from the right table that is from 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 Studentname column will be null for that University. OK, but look, Logan record is not retrieved, But instead Massachusetts Institute of Technology (MIT) record is retrieved because that 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. so that’s what right join is going to do.

Right, join is going to retrieve all the rows from the right table, including the non-matching ones. Left join is going to retrieve all the rows from the left table, including the non-matching ones, whereas inner join is going to retrieve only the matching rows between the tables involved in the join, non-match in rows will be eliminated.

what is the full join?

Full join is going to retrieve all the rows from all the tables, that is from both the tables, that is from the left table and from the table, irrespective of whether they have matching rules or not. So if we were to change this join to 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 is the one present in the left table, but that does not have a corresponding matching row in the right table that is the TblUniversity table.

And this last row is the row that is present in the TblUniversity table. That is the right table, which does not have a corresponding row in the left table., so a full join is going to return all the rows from both the tables, irrespective of whether they have matching records or not. So that’s basically the full join.

And there could be other interview questions as well. Based on these joins but if you understand the basics of joint, then answering any joins related interview questions should be a cakewalk.

For example, the interviewer could ask you, what is the difference between the left join and right join, or what is the difference between right join and full join? Or what is the difference between right join and left join?

Another interesting question-

What is the difference between inner join and join or what is the difference between left and left outer join, right join, and right outer join?

There’s no difference, inner join, or just join, the query is going to be executed in the same way and it is going to perform in the same manner, either from a performance perspective or from the way the query gets executed. That’s not any different, exactly the same thing.the same applies for left join and left outer join, right join and right outer join and full join and full outer join.

That’s it for today. Thank you for reading. Have a great day.

The post Difference between Inner Join ,Left Join and Full Join-Sql appeared first on Software Development | Programming Tutorials.