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
- query does not show result via procedure
- Retrieve a column from a table then insert it to a new table
- Return Multi Row DataSet as Single Row CSV without Temp Table
- Deterministic way to get column type
- Fastest way to perform time average of multiple calculations in SQL?
- Select Minimum value of column A where Column B=1
- PDO lastInsertId() not working for MS SQL
- Multiplying two columns of same table and storing result to the third column of same table
- Classic ASP sql server "operation is not allowed" error
- SQL script than create table and and fill it out file Resources.resx
- How can I put a column in SQL that shows a 1 if the value in another column is unique and 0 if it's duplicate?
- sql select from table then compare with other table in one statement
- Should I Rebuild SQL Server App for 64bit?
- retrieving sql data from each nth row only
- How to select and sort records with biggest column size by characters?
- SQL Aggregate Function Query not Producing Expected Results
- Display hourly based data for 24 hour in SQL Server
- How to change SQL Server Instance TCP Ports to 1433 using CMD?
- SQL pivot count and sum 2 columns
- How to remove unknown characters using T-SQL?
- Replacing Value of Empty Node in SQL XML
- SQL Server DB Project Publish Empty string inserting a Zero
- connecting a java app to external microsoft sql server 2012
- SQL syntax error (SQL injection)
- How do you kill all current connections to a SQL Server 2005 database?
- How to mass update SQL Server Agent Job retry attempts based on Job name
- Does SQL Server 2014 support functions such as ISNOTNULL()?
- Sum of values using a week range from another table
- Set datefirst dynamically by the first day of the year
- UNION on parameters SQL
- Offset subquery in lead/lag
- SQL Server : aggregate function doesn't work?
- Creating a view of multiple joins
- How to restore SQL Server database by backup description?
- SQL query to select the start and end datetime of a value with system versioned tables
- How to integrate while loop into SQL query? (Repeating last result in the column)
- Why am I getting an error message that says "Invalid Object Name" when I've declared my variable in the current batch?
- Optimize for speed a simple stored procedure
- Trying to build an SQL Server query with a specific output
- Datagridview show no data vb.net with SQL Server
- SQL Server CE 4.0 with multiple connections across seperate processes
- If a database is totally encrypted how can a query work?
- Return a single record from a SQL Server Stored Procedure, or use output parameters
- find common rows of matching column from two tables
- Can I set the dynamic path in the query in SQL Server with json?
- What is more powefull between a stored procedure and a view?
- VBS Error, Can't execute a query using WMI Service?
- "argument 'sql' cannot be null" when including "Go" in EF DbMigration
- LocalDb -> Windows Service -> Cannot open database "" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\LOCAL SERVICE'
- Getting the error "Invalid length parameter passed to the LEFT or SUBSTRING function."
- SQL Statement for counting how many times a record is changed
- Is there a difference between NOT (ColumnName LIKE '%a%') and ColumnName NOT LIKE '%a%'
- SQL Server: select newest rows who's sum matches a value
- Insert multiple records for same ID
- SQL Cascade Delete with multiple columns
- How do you change the datatype of a column in SQL Server?
- Merge rows based on date in SQL Server
- Multi-Table Foreign Key & Referential Integrity
- Gracefully terminate long running SQL Server query from ADO.NET
- Stored Procedure with Optional DateTime Parameters in SQL Server
- does snapshot isolation level protects from Phantom read?
- Displaying Data in a DataGridView
- How to get same rownumber() for same values
- Can't connect to Windows Azure Database
- SQL divide by zero error - ISNULL not working?
- SQL Error : Conversion failed when converting date and/or time from character string
- SELECT 5 most recent SQL Server
- How to create simple Insert to SQL Server using Java Persistence API
- Get other columns that corresponds to MIN and MAX value of one column in SQL Server 2008
- Vendor database contains table with 100 user defined string columns need to find all occurrences in that table that are not null
- Using XQUERY/Modify to replace a piece of my XML with value from varchar field
- How to solve "Runtime error Exception has been thrown by the target of an invocation" Am I missing a DLL file?
- Detecting the record that was updated within a Trigger
- Extended TYPE_NAME function that includes datalength
- I want to display data from SQL Server to an ASP.NET textbox based on session of users id
- What is the detriment to storing char data as nvarchar? (And not consistently)
- Stored Procedures - Updating and Inserting
- SQL Server Pre-Login Handshake Acknowledgement Error
- Using Always Encrypted with Entity Framework and Azure Key Vault
- Data ended at 64512 characters - MSSQL // PHP // OPENSuSE // APACHE2
- Select which returns multiple values doesn't work as expected
- Selecting data from more than 2000 databases?
- Using BCP in a SQL query
- MS SQL Server - How to run a query one by one after a delay?
- Convert 7 digit Julian Date to DateTime in SQL?
- Execute SQL Server scripts
- Create an Integrity error for an SQL Server Database for the purpose of Testing
- Entity framework connects to master database for contained database user
- Creating multiple CSV files using SQL
- TSQL - Conditionally "Cross Join" Records
- how do I update SQL columns with true or false based data in another column on the same table?
- SQL Server - CASE within a CASE checking for NULL
- Ajax Error 504 - SQL Stored Procedure saving and returning record
- Create trigger to check whether same day or not in SQL Server?
- Retrieve data quarterly(each 3 month) and also show last day of quarter
- Increment column value by one
- When to use recursive table
- How can i use ' when - between ' statement in sql?
- SQL Server Lookup Functions
- Applying grouped ranking using ROW_NUMBER
- MS SQL bulk user and database creation
- Read both underlying and uncommitted transaction data
- How to install mssql driver (sqlsrv)for Laravel in MAC?
- Creating a table from another
- How to handle iOS emojis in sql server database?
- Group by first column with results from second column as xml
- SQL append 14 hours if time is greater them 10 am
- Two dimensional rank using T-SQL
- SELECT XML FROM TABLE IN SQL SERVER
- Add new calculate column (Average Sales 4 last weeks) to an existing table
- Recursive Decaying Average in Sql Server 2012
- Where to use the column-level encryption of SQL?
- SQL Server host not found error with sequelize
- copy a database within SQL Server Express?
- Find row with max value for each key
- Import multiple Excel files into SQL Server 2008 R2 using SSIS Packages?
- Why does sql server generate stored procedures using sp_executesql statement with string?
- Delphi EMS FireDAC: Cannot open dataset [FDMemTable]
- Find all dependencies of a list of stored procedures
- How secure is using an encrypted appSettings element in your app.config?
- In what format is SQL Server data serialized when it is sent through the network?
- How to import CSV with unrecognized datetime format?
- DBCC SHRINKFILE to truncate the transaction log
- SQL Query for Data Copy to Other Field
- SQL: Cast Not working in SubString
- RANK() Over Partition BY not working
- How to get SQL query Result set in XML format?
- LINQ C# equivalent of LIMIT 0..10 MySQL with TAKE function
- Encrypting Windows Authentication connection strings
- majority vote TSQL
- Student Attendance Report Month-wise SQL Query
- SQL multiple counts from multiple tables
- How to parametrize results from a query to use in a stored procedure
- How to select from duplicate rows from a table?
- insert with select query
- sql query (potentially solvable with pivot / unpivot?)
- How to get a latest data from a cell for each person who satisfies the condition?
- Does the order of columns in an multicolumn non-clustered index matter in SQL Server?
- SQL Equivalent of Oracle's "WHERE () IN"?
- SSIS data flow task does not write records to excel file when executed after tasks that empty the file
- Subquery returned more than 1 value when using Union and string_split()
- vba access mssql inserting data
- Retrieve DataTable info for a table in SQL Server
- SQL Parent child tree data return only completed tree nodes from list
- What is the most efficient way to SELECT data from a second SQL Server instance?
- C# / Microsoft SQL Server 2012: Unicode symbols not displaying correctly (•)
- how to connect to remote SQL Server Express over internet?
- Can I have a SQL View which joins on certain criteria
- Why do two columns gets updated when I specified only one to be updated? and how to correct it?
- Showing only most recent record grouped by substrings of two columns