SQL joins are fundamental operations in relational databases, allowing us to combine data from multiple tables. Two of the most commonly used join types are INNER JOIN and LEFT JOIN. Understanding the difference between these joins is crucial if you intend to use joins in your queries.

In this article, we’ll look at the difference between the INNER JOIN and LEFT JOIN using a simple example.

Key Differences

The INNER JOIN and the LEFT JOIN are designed to do two different things. Here’s the main difference:

• INNER JOIN only returns matched rows, while LEFT JOIN returns all rows from the left table and matched rows from the right table.
• LEFT JOIN will return NULL values for unmatched rows from the right table, while INNER JOIN excludes these rows entirely.

It’s worth noting that there’s also a RIGHT JOIN, which is very similar to the LEFT JOIN, except that it works in reverse – it returns all rows from the right table and matched rows from the left. And a RIGHT JOIN will return NULL values for unmatched rows from the left table.

Sample Database

First, let’s set up our sample database:

-- Create artists table
CREATE TABLE artists (
    artist_id INT PRIMARY KEY,
    artist_name VARCHAR(100) NOT NULL
);

-- Create albums table
CREATE TABLE albums (
    album_id INT PRIMARY KEY,
    album_name VARCHAR(100) NOT NULL,
    artist_id INT,
    release_year INT,
    FOREIGN KEY (artist_id) REFERENCES artists(artist_id)
);

-- Insert sample data for NZ artists
INSERT INTO artists (artist_id, artist_name) VALUES
(1, 'Lorde'),
(2, 'Split Enz'),
(3, 'Crowded House'),
(4, 'Fat Freddy''s Drop'),
(5, 'Brooke Fraser'),
(6, 'The Naked and Famous'),
(7, 'Kimbra');

-- Insert sample data for albums
INSERT INTO albums (album_id, album_name, artist_id, release_year) VALUES
(1, 'Pure Heroine', 1, 2013),
(2, 'Melodrama', 1, 2017),
(3, 'True Colours', 2, 1980),
(4, 'Woodface', 3, 1991),
(5, 'Based on a True Story', 4, 2005),
(6, 'What to Do with Daylight', 5, 2003),
(7, 'Passive Me, Aggressive You', 6, 2010),
(8, 'Solar Power', 1, 2021);

Now that we have our sample data, let’s explore the differences between the INNER JOIN and the LEFT JOIN.

The INNER JOIN

An INNER JOIN returns only the rows that have matching values in both tables. It’s used when we want to retrieve data that exists in both tables.

Here’s an example that lists all artists and their albums:

SELECT 
    a.artist_name, 
    al.album_name, 
    al.release_year
FROM 
    artists a
INNER JOIN 
    albums al ON a.artist_id = al.artist_id
ORDER BY 
    a.artist_name, al.release_year;

Result:

artist_name           album_name                  release_year
--------------------  --------------------------  ------------
Brooke Fraser         What to Do with Daylight    2003        
Crowded House         Woodface                    1991        
Fat Freddy's Drop     Based on a True Story       2005        
Lorde                 Pure Heroine                2013        
Lorde                 Melodrama                   2017        
Lorde                 Solar Power                 2021        
Split Enz             True Colours                1980        
The Naked and Famous  Passive Me, Aggressive You  2010        

Notice that all artists in the results have at least one album. But there is actually one artist in our database – Kimbra – that doesn’t have any albums. Kimbra isn’t listed in these results. And that’s because we used an INNER JOIN instead of a LEFT JOIN.

The LEFT JOIN

A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there’s no match, NULL values are returned for the right table’s columns.

This time, let’s list all artists and their albums, including artists with no albums:

SELECT 
    a.artist_name, 
    al.album_name, 
    al.release_year
FROM 
    artists a
LEFT JOIN 
    albums al ON a.artist_id = al.artist_id
ORDER BY 
    a.artist_name, al.release_year;

Result:

artist_name           album_name                  release_year
--------------------  --------------------------  ------------
Brooke Fraser         What to Do with Daylight    2003        
Crowded House         Woodface                    1991        
Fat Freddy's Drop     Based on a True Story       2005        
Kimbra                null                        null        
Lorde                 Pure Heroine                2013        
Lorde                 Melodrama                   2017        
Lorde                 Solar Power                 2021        
Split Enz             True Colours                1980        
The Naked and Famous  Passive Me, Aggressive You  2010        

This time Kimbra is listed, along with null in both the album_name and release_year columns.

In conclusion, choosing between INNER JOIN and LEFT JOIN depends on your specific data requirements. Use INNER JOIN when you only need data that exists in both tables, and LEFT JOIN when you want to include all records from the left table, regardless of matches in the right table.

More Joins

See my SQL Joins Tutorial for a rundown on the different join types, along with examples.