SQL INNER JOIN vs LEFT JOIN with real examples

#Why it matters

The inner join vs left join choice is the decision you make in almost every analyst query, because the data you need never sits in one table. SQL appears in 88% of the Data Analyst and 80% of the Data Engineer job descriptions we read, and joins are where most wrong numbers come from: rows that silently disappear, or rows that silently multiply. This lesson gives you the habits that catch both before your manager does.

#The concept

#Start from the question: which rows must survive?

A join puts columns from two tables side by side, matching rows on a key. The two joins you will use 95% of the time differ only in what happens to rows with no match:

So before you write the join, say the sentence: "I need one row per customer, including customers with no orders." The table in that sentence goes on the left, and the join is a LEFT JOIN. If the sentence is "one row per order, with its customer's country", every order has a customer, and an INNER JOIN is fine (but check that it really is).

JOIN on its own means INNER JOIN. LEFT JOIN and LEFT OUTER JOIN are the same thing.

#Check the key on the lookup side

A join matches every pair of rows whose keys are equal. If the right table has two rows with the same key, each left row that matches it appears twice. Before joining to a lookup table, prove the key is unique:

SELECT COUNT(*) AS rows, COUNT(DISTINCT key) AS distinct_keys FROM lookup;

If the two numbers differ, the join will duplicate rows. The join fan-out lesson later in this module deals with that case in depth.

#Count rows before and after every join

The cheapest data-quality check in SQL: count the left table, then count the joined result.

• After an INNER JOIN, fewer rows means some left rows had no match. Find out which before you accept it.

• After a LEFT JOIN, more rows than the left table means the right side has duplicate keys for some rows. That is the answer to a favourite interview question: a LEFT JOIN returns at least as many rows as the left table, and can return more.

#WHERE on the right table turns a LEFT JOIN into an INNER JOIN

This is the most common join bug in real code. Rows that found no match have NULL in every right-side column. A WHERE condition on a right-side column (WHERE i.InvoiceDate >= '2025-12-01') is unknown for NULL, so those rows are filtered out, and you are back to an inner join without noticing. Put conditions that describe which right-side rows to attach in the ON clause instead. Conditions about the left table stay in WHERE.

#The anti-join: find what is missing

"Which customers never ordered?", "which products never sold?", "which zones had no pickups?": all are answered by the same pattern.

SELECT l.*
FROM left_table l
LEFT JOIN right_table r ON r.key = l.key
WHERE r.key IS NULL;

Test a column that can never be NULL in a real match, usually the right table's primary key. NOT EXISTS does the same job and is covered in the subqueries lesson later in this module.

#ON, USING and aliases

• ON a.GenreId = g.GenreId works for any condition, including different column names on each side.

• USING (GenreId) is a shortcut when both columns have the same name. It outputs the column once. SQL Server does not support it.

• Short aliases (t for Track, g for Genre) keep queries readable, and they become mandatory when the same table appears twice.

#Joining the same table twice

When a table holds two references to the same lookup (pickup zone and dropoff zone, buyer and seller, a support rep and that rep's manager), join the lookup twice with two aliases. Each alias is an independent copy of the table.

#Keys that look equal but are not

A join compares values exactly. 'USA ' with a trailing space does not equal 'USA', and 'canada' does not equal 'Canada' in most databases. Number-versus-text keys are even trickier: PostgreSQL refuses to compare text = integer, DuckDB and SQLite quietly convert one side, and MySQL converts both to numbers. Clean keys with TRIM, LOWER and CAST (ideally once, when the data is loaded), and always check how many rows found no match.

#Worked example: joins on the Chinook music store

The Chinook database (MIT licence, lerocha/chinook-database) models a digital music shop: artists, albums, tracks, customers, invoices and employees. Download Chinook_Sqlite.sqlite from the repository's ChinookDatabase/DataSources folder and open it with the sqlite3 command-line tool or DB Browser for SQLite. In sqlite3, run .headers on and .mode column so results look like the outputs below.

#Step 1: a safe INNER JOIN, with checks

Question: how many tracks does each genre have?

SELECT COUNT(*) AS tracks FROM Track;
SELECT COUNT(*) AS genre_rows, COUNT(DISTINCT GenreId) AS distinct_keys FROM Genre;
SELECT COUNT(*) AS rows_after_inner FROM Track t INNER JOIN Genre g ON g.GenreId = t.GenreId;

tracks
------
3503
 
genre_rows  distinct_keys
----------  -------------
25          25
 
rows_after_inner
----------------
3503

Genre keys are unique (25 rows, 25 keys), and the join kept all 3,503 tracks, so no track lost its genre and none was duplicated. Now the answer is trustworthy:

SELECT g.Name AS genre, COUNT(*) AS tracks
FROM Track t
JOIN Genre g ON g.GenreId = t.GenreId
GROUP BY g.Name
ORDER BY tracks DESC
LIMIT 5;

genre               tracks
------------------  ------
Rock                1297
Latin               579
Metal               374
Alternative & Punk  332
Jazz                130

JOIN Genre USING (GenreId) gives the same 3,503 rows; use whichever your team prefers, but ON works everywhere.

#Step 2: INNER and LEFT give different populations

Question: how many artists are in the catalogue, and how many have albums?

SELECT COUNT(*) AS artists FROM Artist;
SELECT COUNT(*) AS rows_inner, COUNT(DISTINCT ar.ArtistId) AS artists_kept
FROM Artist ar JOIN Album al ON al.ArtistId = ar.ArtistId;
SELECT COUNT(*) AS rows_left, COUNT(DISTINCT ar.ArtistId) AS artists_kept
FROM Artist ar LEFT JOIN Album al ON al.ArtistId = ar.ArtistId;

artists
-------
275
 
rows_inner  artists_kept
----------  ------------
347         204
 
rows_left  artists_kept
---------  ------------
418        275

Read these three results carefully, because they contain the whole lesson:

• The INNER JOIN keeps only 204 of 275 artists. 71 artists vanished because they have no album.

• Both joins return more rows than artists (347 and 418), because an artist with three albums appears three times. A LEFT JOIN can return more rows than its left table.

• The LEFT JOIN keeps all 275 artists: 347 artist-album rows plus 71 rows with NULL album columns gives 418.

If your report says "number of artists" and you used an inner join with COUNT(*), you would print 347, which is wrong twice.

#Step 3: the anti-join

Which artists have no albums?

SELECT ar.ArtistId, ar.Name
FROM Artist ar
LEFT JOIN Album al ON al.ArtistId = ar.ArtistId
WHERE al.AlbumId IS NULL
ORDER BY ar.ArtistId
LIMIT 5;

ArtistId  Name
--------  --------------------------
25        Milton Nascimento & Bebeto
26        Azymuth
28        João Gilberto
29        Bebel Gilberto
30        Jorge Vercilo

Replace the column list with COUNT(*) and you get 71, exactly the gap between 275 and 204. When the numbers reconcile like that, you understand your join.

#Step 4: the WHERE clause that breaks a LEFT JOIN

Question: December 2025 sales for every customer, including those who bought nothing. The invoices run from 2021-01-01 to 2025-12-22. Here is the version most people write first:

SELECT COUNT(*) AS customers_listed, ROUND(SUM(i.Total), 2) AS sales
FROM Customer c
LEFT JOIN Invoice i ON i.CustomerId = c.CustomerId
WHERE i.InvoiceDate >= '2025-12-01';

customers_listed  sales
----------------  -----
7                 38.62

Seven customers, not 59. The 52 customers without a December invoice got NULL dates, NULL >= '2025-12-01' is not true, and WHERE threw them away. Move the date condition into ON, so it decides which invoices to attach rather than which customers to keep:

SELECT COUNT(*) AS customers_listed, COUNT(i.InvoiceId) AS with_dec_invoice, ROUND(SUM(i.Total), 2) AS sales
FROM Customer c
LEFT JOIN Invoice i ON i.CustomerId = c.CustomerId AND i.InvoiceDate >= '2025-12-01';

customers_listed  with_dec_invoice  sales
----------------  ----------------  -----
59                7                 38.62

All 59 customers are back. COUNT(i.InvoiceId) counts only non-NULL values, so it tells you how many actually bought. For the per-customer report, turn the missing sales into zero with COALESCE:

SELECT c.FirstName || ' ' || c.LastName AS customer, c.Country,
       COALESCE(SUM(i.Total), 0) AS dec_sales
FROM Customer c
LEFT JOIN Invoice i ON i.CustomerId = c.CustomerId AND i.InvoiceDate >= '2025-12-01'
GROUP BY c.CustomerId
ORDER BY dec_sales DESC, customer
LIMIT 4;

customer          Country   dec_sales
----------------  --------  ---------
Terhi Hämäläinen  Finland   13.86
Madalena Sampaio  Portugal  8.91
Robert Brown      Canada    5.94
Victor Stevens    USA       3.96

|| concatenates text in SQLite, PostgreSQL and DuckDB; MySQL needs CONCAT().

#Step 5: the same lookup twice

Each customer has a support rep (Customer.SupportRepId), and each employee has a manager (Employee.ReportsTo). To show customer, rep and the rep's manager in one row, join Employee twice:

SELECT c.FirstName || ' ' || c.LastName AS customer,
       rep.FirstName || ' ' || rep.LastName AS support_rep,
       boss.FirstName || ' ' || boss.LastName AS rep_manager
FROM Customer c
JOIN Employee rep  ON rep.EmployeeId  = c.SupportRepId
LEFT JOIN Employee boss ON boss.EmployeeId = rep.ReportsTo
ORDER BY c.CustomerId
LIMIT 4;

customer           support_rep    rep_manager
-----------------  -------------  -------------
Luís Gonçalves     Jane Peacock   Nancy Edwards
Leonie Köhler      Steve Johnson  Nancy Edwards
François Tremblay  Jane Peacock   Nancy Edwards
Bjørn Hansen       Margaret Park  Nancy Edwards

The second join is a LEFT JOIN on purpose: the General Manager has no manager (ReportsTo is NULL), and if a rep were ever promoted to that role, an inner join would silently drop their customers. The same shape answers "pickup zone name and dropoff zone name" for taxi trips: one zone lookup, two aliases.

#Step 6: keys that look equal and are not

Suppose a colleague hands you a small mapping table typed in a spreadsheet, with a trailing space after USA and canada in lower case:

CREATE TABLE country_region (country TEXT, region TEXT);
INSERT INTO country_region VALUES ('USA ', 'Americas'), ('canada', 'Americas'),
  ('Brazil', 'Americas'), ('France', 'Europe'), ('Germany', 'Europe');

SELECT COALESCE(r.region, '(no match)') AS region, COUNT(*) AS customers
FROM Customer c
LEFT JOIN country_region r ON r.country = c.Country
GROUP BY 1
ORDER BY customers DESC;

region      customers
----------  ---------
(no match)  45
Europe      9
Americas    5

Only Brazil's 5 customers reached "Americas"; the 13 US and 8 Canadian customers silently fell into "no match". No error, no warning. Clean both sides in the join (or, better, fix the mapping table):

SELECT COALESCE(r.region, '(no match)') AS region, COUNT(*) AS customers
FROM Customer c
LEFT JOIN country_region r ON LOWER(TRIM(r.country)) = LOWER(TRIM(c.Country))
GROUP BY 1
ORDER BY customers DESC;

region      customers
----------  ---------
Americas    26
(no match)  24
Europe      9

5 + 13 + 8 = 26. The remaining 24 genuinely have no mapping (the table lists only five countries). Wrapping key columns in functions also stops the database from using an index on them, which is another reason to clean keys once at load time instead.

#Common mistakes

• Using INNER JOIN for a "for every X" question. Customers, products or days without a match disappear from the report. Put the population table on the left and use LEFT JOIN.

• Filtering the right table in WHERE. It throws away the unmatched rows a LEFT JOIN was meant to keep. Move the condition into ON.

• COUNT(*) after a LEFT JOIN. It counts the NULL rows too. Count a right-side column (COUNT(i.InvoiceId)) to count matches.

• Joining to a lookup with duplicate keys. Rows multiply and sums inflate. Check COUNT(*) against COUNT(DISTINCT key) first.

• Anti-join on a nullable column. WHERE r.some_column IS NULL also catches matched rows where that column happens to be empty. Test the right table's primary key.

• Ignoring dirty keys. Trailing spaces, case and text-versus-number keys match nothing without an error. Count the unmatched rows every time.

• Ambiguous column names. SELECT Name after joining Artist and Genre fails or picks the wrong one. Prefix every column with its alias.

#Interview angle

#Practice task

Use the same Chinook database.

1. Count tracks, then join Track to Album and to Artist. Prove with row counts that no track is lost or duplicated.

2. List the 5 artists with the most tracks, with artist name and track count.

3. Find every track that has never been sold (no row in InvoiceLine), and count them.

4. Produce one row per employee with their title and the number of customers they support, including employees who support nobody (they should show 0).

5. For every invoice, show the billing country and the name of the customer's support rep and that rep's manager. Check that the row count equals the number of invoices.

Done when every query has a row-count check next to it and the numbers reconcile.

-- 1
SELECT COUNT(*) FROM Track;                                   -- 3503
SELECT COUNT(*) FROM Track t
JOIN Album al ON al.AlbumId = t.AlbumId
JOIN Artist ar ON ar.ArtistId = al.ArtistId;                  -- also 3503

-- 2
SELECT ar.Name, COUNT(*) AS tracks
FROM Track t
JOIN Album al ON al.AlbumId = t.AlbumId
JOIN Artist ar ON ar.ArtistId = al.ArtistId
GROUP BY ar.ArtistId
ORDER BY tracks DESC
LIMIT 5;

-- 3
SELECT COUNT(*) AS never_sold
FROM Track t
LEFT JOIN InvoiceLine il ON il.TrackId = t.TrackId
WHERE il.InvoiceLineId IS NULL;

-- 4
SELECT e.FirstName || ' ' || e.LastName AS employee, e.Title,
       COUNT(c.CustomerId) AS customers
FROM Employee e
LEFT JOIN Customer c ON c.SupportRepId = e.EmployeeId
GROUP BY e.EmployeeId
ORDER BY customers DESC;

-- 5
SELECT i.InvoiceId, i.BillingCountry,
       rep.FirstName || ' ' || rep.LastName AS rep,
       boss.FirstName || ' ' || boss.LastName AS manager
FROM Invoice i
JOIN Customer c ON c.CustomerId = i.CustomerId
LEFT JOIN Employee rep ON rep.EmployeeId = c.SupportRepId
LEFT JOIN Employee boss ON boss.EmployeeId = rep.ReportsTo;   -- 412 rows, same as Invoice

#Further reading

• SQLite SELECT documentation: how joins, ON and USING are processed.

• PostgreSQL tutorial: joins between tables: the same ideas in PostgreSQL, including outer joins.

• Chinook database: the schema diagram and versions for PostgreSQL, MySQL and SQL Server.

• Use The Index, Luke: join operations: why join keys and functions on them matter for performance.