Difference between Inner join and Left join

SQL Join is used for combining the rows between two or more tables using common fields/columns between these tables in a relational database. The goal of joining table is to extract the meaningful list of data. Joins will be obtained from the term called predicate. “Join” is a term used to combine fields of two or more tables by using common values between the tables.

There are different types of joins in SQL and they are –

• INNER JOIN
• CROSS JOIN
• SELF JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

Difference between Inner Join vs Left join

For demonstration purpose let’s create tables and let’s call it as – “tClass” and “tStudent”

tClass table structure

tStudent table structure

Inner join

Inner join matches the common records in two tables joined. Here in the above example we have “ClassID” in tclass table and “ClassID” in tstudent table. Below query is used to fetch the list of students which are in Class “10th”

SELECT s.StudentID, s.StudentName
FROM tClass c 

INNER JOIN tstudent s ON c.ClassID = s.ClassID

WHERE c.ClassID = 10

So above query is used to fetch the list of students which are studying in Class – “10th”. So here in “tstudent” table all students are stored irrespective of their classes and similarly in “tclass” table all classes are stored. So as the result of the above query, matched records between tstudent and tclass tables are searched and displayed. In the introduction part there was an introduction to the term called “predicate”, in the above query it is – “ON c.ClassID = s.ClassID”, which is one of the essential part of the join.

Note : There are multiple keys associated to each table like in tStudent table have “StudentID” as a primary key field and “ClassID” as a foreign key which inturn refer to the table – “tClass”.

• In Inner join each record of table A Is matched with each record of Table B and the matched records are then be displayed in the resultant table.
• JOIN word can be used instead of INNER JOIN, both meant the same.
• INNER JOIN is the intersection of data between table A and table B.

The above query can be rewritten without using inner join like below but the performance will be impacted compared to inner join –

SELECT s.StudentID, s.StudentName
FROM tClass c , tstudent s

WHERE c.ClassID = s.ClassID AND c.ClassID = 10

Left Join

• Left join will return all the rows from the left table and matching rows from right table.
• In case of no match with right side table it will return NULL value.
• “Left Join” and “Left Outer Join” are used interchangeably because records which are returned will be the same with either of these.

Below query is used to fetch the all the classes and the students are in that class.

SELECT s.StudentID, s.StudentName
FROM tClass c 

LEFT JOIN tstudent s ON c.ClassID = s.ClassID

Above can be rewritten using “LEFT OUTER JOIN” as –

SELECT s.StudentID, s.StudentName
FROM tClass c 

LEFT OUTER JOIN tstudent s ON c.ClassID = s.ClassID

As shown in the above figure all the rows from “tclass” are fetched and the students of the respective classes are fetched. In case there are no students found in that class then still class will be fetched as it is from the left table and NULL will be displayed in place of “StudentID” and “StudentName”.

There is another join called “FULL OUTER JOIN” which is similar to LEFT JOIN except one feature i.e, “FULL OUTER JOIN” returns all the rows from the left table as well as from the right table.

“RIGHT OUTER JOIN” is just the opposite of LEFT JOIN. In “RIGHT OUTER JOIN” it takes all the rows from the right table and selected rows from the left table.

Examples of using both these joins

Let’s have an example for both these joins. For demonstration purpose let’s create two tables called – “Category” and “Product”.

Category Table

CategoryID INT - PK

CategoryName VARCHAR(50)
CategoryAdded DATETIME

CategoryDeleted DATETIME

Product Table

ProductID INT - PK

ProductName VARCHAR(50)
CategoryID INT - FK

ProductDeleted DATETIME

ProductAdded DATETIME

Above is the list of two tables and the list of columns of the tables and as you can see “CategoryID” is foreign key and “CategoryID” and “ProductID” is primary keys in tables – “Category” and “Product” respectively.

Query – Get all products of all categories –

SELECT p. ProductID , p. ProductName, c. CategoryName 

FROM Category c INNER JOIN Product p ON c.CategoryID = p. CategoryID

Here in above query in case there are no products in some category then category would not be fetched at all. So to make sure that all the categories are listed use LEFT join instead of INNER JOIN. So the query can be changed like this –

SELECT p. ProductID , p. ProductName, c. CategoryName 

FROM Category c LEFT JOIN Product p ON c.CategoryID = p. CategoryID

So all the categories are displayed here irrespective of presence of products in that category.