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.
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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?