Emil Drkusic

Learn SQL: INNER JOIN vs LEFT JOIN

January 16, 2020 by

INNER JOIN vs LEFT JOIN, that is the question. Today, we’ll briefly explain how both of these two join types are used and what is the difference. We’ll go through this topic again later when we’ll expand our model and be able to write much more complex queries.

Changes in the data

Before we compare INNER JOIN vs LEFT JOIN, let’s see what we currently know. So far, in this series, we’ve explained database basics – how to create database and tables, how to populate tables with data and check what’s stored in them using simple queries. We’ve even joined two tables in the previous article. Now we’re ready for the next step.

SQL data model

But before we move to it, let’s make just one minor change to our data. We’ll add 2 rows in the country table, using the following INSERT INTO commands:

Now we’ll check the contents of both tables:

INNER JOIN vs LEFT JOIN

You can easily notice that we have 2 new rows in the table country, one for Spain and one for Russia. Their ids are 6 and 7. Also notice, that in the city table there is no country_id with value 6 or 7. This simply means that we don’t have a city from Russia or Spain in our database. We’ll use this fact later.

INNER JOIN

Let’s discuss these two queries:

The result they return is presented on the picture below:

SELECT query

Both queries return exactly the same result. This is not by accident but the result of the fact that this is the same query written in two different ways. Both ways are correct, and you can use any of them.

In the first query, we listed all tables we use in the FROM part of the query (FROM country, city) and then went with the join condition in the WHERE part of the query (WHERE city.country_id = country.id). In case we forgot to write down this join condition, we would have the Cartesian product of both tables.

In the second query, we have only one table in the FROM part of the query (FROM country) and then we have the second table and the JOIN condition in the JOIN part of the query (INNER JOIN city ON city.country_id = country.id).

While both queries are well-written, I would suggest that you always use INNER JOIN instead of listing tables and joining them in the WHERE part of the query. There are a few reasons for that:

  • Readability is much better because the table used and related JOIN condition are in the same line. You can easily see if you omitted the JOIN condition or not
  • If you want to use other JOINs later (LEFT or RIGHT), you couldn’t do that (easily) unless you’ve used INNER JOIN before that

Now, let’s comment on what queries actually returned:

  • All pairs of countries and cities that are related (via foreign key)
  • We don’t have 2 countries in the list (Spain and Russia), because they don’t have any related city in the city table

LEFT JOIN

I’ll repeat this – “We don’t have 2 countries on the list (Spain and Russia) because they don’t have any related city in the city table“. This shall prove crucial when comparing INNER JOIN vs LEFT JOIN.

In some cases, we want to have even these records in our results. For example, you simply want to see in the result that these countries don’t have related records in another table. This could be part of some control, or maybe just counting cases, etc. No matter what the motivation behind that desire is, we should be technically able to do that. And we are. In databases, LEFT JOIN does exactly that.

The result of LEFT JOIN shall be the same as the result of INNER JOIN + we’ll have rows, from the “left” table, without a pair in the “right” table. We’ll use the same INNER JOIN query and just replace the word INNER with LEFT. This is the result:

INNER JOIN vs LEFT JOIN

You can easily notice, that we have 2 more rows, compared to the result of the INNER JOIN query. These are rows for Russia and Spain. Since they both don’t have any related city, all city attributes in these two rows have NULL values (are not defined). That is the biggest difference when comparing INNER JOIN vs LEFT JOIN.

RIGHT JOIN

You’ll at least hear about the RIGHT JOIN. It’s rarely used because it returns the same result as the LEFT JOIN. On the other hand, queries which use LEFT JOIN are much easier to read because we simply list tables one after the other.

This is the equivalent of the previous query using the RIGHT JOIN:

RIGHT JOIN query

You can notice that returned values are the same, only in this case values from the city table are in the first 5 columns, and country-related values come after them.

INNER JOIN vs LEFT JOIN

INNER JOIN vs LEFT JOIN? Actually, that is not the question at all. You’ll use INNER JOIN when you want to return only records having pair on both sides, and you’ll use LEFT JOIN when you need all records from the “left” table, no matter if they have pair in the “right” table or not. If you’ll need all records from both tables, no matter if they have pair, you’ll need to use CROSS JOIN (or simulate it using LEFT JOINs and UNION). More about that in the upcoming articles.

Table of contents

Learn SQL: CREATE DATABASE & CREATE TABLE Operations
Learn SQL: INSERT INTO TABLE
Learn SQL: Primary Key
Learn SQL: Foreign Key
Learn SQL: SELECT statement
Learn SQL: INNER JOIN vs LEFT JOIN
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Learn SQL: Aggregate Functions
Learn SQL: How to Write a Complex SELECT Query
Learn SQL: The INFORMATION_SCHEMA Database
Learn SQL: SQL Data Types
Learn SQL: Set Theory
Learn SQL: User-Defined Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Views
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries
Learn SQL: SQL Query examples
Learn SQL: Create a report manually using SQL queries
Learn SQL: SQL Server date and time functions
Learn SQL: Create SQL Server reports using date and time functions
Learn SQL: SQL Server Pivot Tables
Learn SQL: SQL Server export to Excel
Learn SQL: Intro to SQL Server loops
Learn SQL: SQL Server Cursors
Learn SQL: SQL Best Practices for Deleting and Updating data
Learn SQL: Naming Conventions
Learn SQL: SQL-Related Jobs
Learn SQL: Non-Equi Joins in SQL Server
Learn SQL: SQL Injection
Learn SQL: Dynamic SQL
Learn SQL: How to prevent SQL Injection attacks
Emil Drkusic
Latest posts by Emil Drkusic (see all)
SQL commands, T-SQL

About Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer. His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby... You can find him on LinkedIn View all posts by Emil Drkusic

168 Views