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
- How to restore SQL Server 2014 database to SQL Server Express 2012
- Convert "\n" to actual newline in SQL Server
- CTE and FOR XML to generate nested XML
- Efficiently Shred Element Centric XML TSQL
- Is there an index that will speed up a CTE?
- Turning Percentile_cont/disc (Median) into scalar function
- TOP performance issue
- SQL : Stored procedure execution error
- Assign variable with distinct records
- SQL Server query for date format
- How to do a json query in C#?
- SSIS control flow stops after sql task
- disconnected sql server when run stored procedure
- One Controller Multiple Tables
- Table Valued Parameter has slow performance because of table scan
- SSIS 2008 R2 Excel Connection Manager Issue
- How can I count just the active rows and not the soft deletes
- Pivot n times for Select statement in T-SQL
- sql server avg_user_impact
- Remove duplicate depending on Date value
- SQL query to feed second query
- Dynamically select distinct current and previous columns from a sql table
- Select latest row value for multiple rows
- T-SQL SELECT TOP returns duplicates
- Convert datatype of existing data from Char to DateTime with SQL
- how to import data from sql-server2008 to excel with visual studio express 2013
- Way of searching 30,000 SQL Records
- Drop all tables in a SQL Server database from Batch file?
- How to avoid divided by zero error
- SQL - similar data in column
- Wildfly using MS SQL Server: why does it take two machines more time to process a job than one machine?
- How to print GETDATE() in SQL Server with milliseconds in time?
- Microsoft Sync Framework : Cannot enumerate changes at the RelationalSyncProvider for table 'Table Name'
- Generic EF6 Repository Method Doesn't Generate Correct SQL
- How to select valid SQL Server datatypes?
- SQL Server Custom Identity Column
- Using Pentaho Community Edition with SQL Server 2005
- How to retrieve a single value from the database using Dapper
- SQL Server uncorrelated subquery very slow
- Trigger insert not working with insert..select from statement
- conditional where statement in tsql?
- Bulk Loads, Indexes and Data Truncation: How do you optimize?
- How can I prevent a record inserted by an SQL trigger attempting to set the identity column
- SQL Server Always encrypt collation incompatibility on insert
- What is the difference between Index Scan, Table Scan, and an Index Seek?
- best way to access mssql database with python 3.3
- How to parameterize SQL query when the parameter can sometimes be NULL?
- Homework - Getting DateTime in custom format in MS SQL
- How can I remove accents on a string?
- SQL Statement - How can Improve speed with indexing
- ssis moving data between sql and access databases
- Selecting 7th Person (row) and get 7 as result
- Taking a SQL Database with me for a presentation
- Sql Server - Group rows
- Split One table into Two in SQL Server 2008
- ASP.NET MVC 3, SQL Server
- Spatial Data Type
- SqlBulkCopy rows from one table to another but with different columns order
- How to connect to mssql database in linux with GO
- How does UPDATE work?
- Process for changing database passwords and avoiding website downtime
- Select a unique value throughout all the rows having a particular value for column
- Optimize assigning values to local variables in T-SQL
- Change Primary Key to Composite Key (Primary Key already exists)
- How to add NULL vs NOT NULL contraint based on data in another column?
- GROUP BY with partial ROLLUP
- SQL extract data to Excel using Powershell
- Stored Procedure Returns NULLS
- insert then select scope_identity() generates duplicate rows
- Replace consecutive identical row occurences to single row based on same Id - SQL Server
- Is it possible to hint at EntityFramework Core to use an inner join instead of an EXISTS sub-query for navigation property collection conditions?
- How to list the stored procedures that contains more than seven join operations in SQL Server database?
- The best database for a C++ and java applications
- Setting A node only, to restricted mode. in AOAG instance
- Compare two records in a SQL Server table and delete unwanted records
- Find all children who are not parent
- Query with reserve word column used to work in SQL Server 2008 no longer works with SQL Server 2012
- I'm having a SQL Insert issue in my gridview
- Copy data from one table and save it into another table in different database on different SQL Server
- New to PIVOT in SQL
- Best choice to store a list of ints in mssql
- Querying JIRA using SQL Server to return historical statuses
- "The semaphore timeout period has expired" on connect to MS SQLServer inside WIndows Container
- SQL Server 2012: Copy old data from "Production" to "Archive"-db each month
- How can I ensure that a JDBC batch insert is done atomically?
- one sql command with two connection string
- Entity Framework - Saving foreign key referring to new record
- SQL Server Combination query
- Loop inside SELECT in SQL Server query
- Weird table's data in SQL server
- Create SQL Server table from user-defined class
- ADO.NET, SQL Server, C#: how to check if a certain parameter does not exist before performing an Insert in a database
- T-SQL Query to check if related col is true and update another table col
- Can SSRS support multi-tenant usage?
- TSQL Logic in SSRS
- How to Speed Up Simple Join
- combobox value in c sharp
- Convert IP to 32-bit Binary in TSQL
- MS SSQL: How to use case when as exec parameter
- SQL Table design question
- How to avoid Deadlock between Insert/Delete statements due to non clustered non unique indexes!
- Index does Not affect query performance in indexed views
- SQL Insert parameter value and value from another table
- Getting single records from multiple rows by id in one column
- how to know next primary key value of table without inserting record in sql server?
- Storing a lot of files in the database
- select statement where clause two conditions
- Parse SQL canonical date with c#
- Insert New data into multiple tables with Foreign Key simultaneously in One query in sql server
- How to change default string representation of GETDATE() to include milliseconds?
- Risk of view any definition, view server state
- Parameters causing query to slow down
- Adding a index on my table for this query
- MS SQL and Java: cannot find table variable during call stored procedure
- Repair/Remove/Install processes for SQL Server 2012 Express get stuck or hung
- Provide status of an Activity based on date column in a view in SQL Server
- Connection string work only on my computer
- How to avoid using duplicate savepoint names in nested transactions in nested stored procs?
- select other column which having min value in subquery
- The query has been canceled because the estimated cost of this query exceeds the configured threshold
- What is the meaning of the prefix N in T-SQL statements and when should I use it?
- Conversion failed when converting the varchar value 'dynamic sql query' to data type int
- Dynamic SQL - Picking the values from the temp table
- time format and string format
- SQL Server: What's wrong with this dynamic pivot?
- Hot to auto-generate TWO unique identifiers for an association table in SQL 2008's Design view
- How to reference columns by name based on comma separated value in another column?
- TransactSQL to run another TransactSQL script
- What is the best way to summarize interval data?
- Sql Server CTE syntax error... help, please?
- Simple Sql Procedure with/without cursor
- C# DataTable into SQL Server stored procedure
- SQL IF statement?
- Convert Oracle query to SQL Server 2008
- Alter column length doesn't update syscolumns length
- using BCP to fill a column of a table with a single value
- How to simulate UNPIVOT in Access?
- Get values from joined tables using SqlDataReader
- Add a row to a sql database which has a identity primary key
- SQL Function Error: The multi-part identifier "T.acKey" could not be bound
- SQL Server : update a record foreign key references to another
- Is there any difference between And (Condition1 or Condition2) vs And Condition1 or Condition2 in SQL
- Why does my SQL code throw a conversion error?
- How to aggregate data for non-working dates?
- Issues while extracting data from corrupted database
- DB Agnostic ASP.Net?
- Conditional Operator in SQL Where Clause
- Updating a part of string in column MS SQL
- Trim SQL field and use ISNULL
- Call Rest TaxJar API from SQL Server Code