SQL Joins: Left Join vs Inner Join

In Data Industry

What is SQL JOIN?

SQL is a Structured Query Language. Join is SQL instruction to combine different sets of data (i.e. two tables) together.

So let’s say there are two tables of data, one of them has information about people firts_name, last_name, id, and the second table has information of people exact location and you need to combine this data.

Visually you will take the firts_name and the last_name and then find the location ID and put in one column. Here is how it will be done in SQL:

Gif is from the following youtube video https://www.youtube.com/watch?v=9yeOJ0ZMUYw&vl=en

Difference between LEFT JOIN and INNER JOIN

LEFT JOIN returns all rows from left table including non-matching rows. 

The image is from mode.com

INNER JOIN returns only the matching rows between the tables involved in the JOIN.

The image is from w3schools.com

When using INNER JOIN the command is to select only rows that match an ON condition. If there is no row that match the ON condition then there will not be any results shown.

LEFT JOIN will return all the data from the first table no matter if there are any matches with the second table or not and will fill the missing data with NULL.

Performance difference between INNER JOIN and LEFT JOIN

There is no such thing. The performance of both INNER JOIN and LEFT JOIN depends on how many JOINS one is doing and whether the columns are indexed or not. Also doing whole 9 to 10 tables scans together for each JOIN could slow down the process. When some of the tables are very small let’s say under 10 rows or when the tables don’t have enough indexes to cover the query then LEFT JOIN could be faster than INNER JOIN. So the circumstances are very important.