SQL Help

LEFT JOIN vs INNER JOIN: the difference, explained

Updated August 2026 · First published 2006 by David J. Lake

All four joins in one table

Everything below is those four rows, made concrete.

A worked example

Three tables: players, teams, and a linking table saying who plays where.

CREATE TABLE players (
  player_id  INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(30),
  last_name  VARCHAR(40)
);

INSERT INTO players (first_name, last_name) VALUES
('Bukayo','Saka'), ('Jordan','Henderson'), ('Erling','Haaland');

CREATE TABLE teams (
  team_id   INT AUTO_INCREMENT PRIMARY KEY,
  team_name VARCHAR(60)
);

INSERT INTO teams (team_name) VALUES
('Arsenal'), ('Manchester City'), ('Newcastle United');

CREATE TABLE squad (
  player_id INT,
  team_id   INT,
  PRIMARY KEY (player_id, team_id)
);

INSERT INTO squad VALUES (1,1), (3,2);   -- Henderson is unattached; Newcastle has nobody

INNER JOIN: only the matches

SELECT p.player_id, p.first_name, p.last_name, t.team_name
FROM players AS p
INNER JOIN squad AS s ON s.player_id = p.player_id
INNER JOIN teams AS t ON t.team_id   = s.team_id;

Henderson vanishes: he has no row in squad, and an inner join only keeps matches. Newcastle vanish too, from the other side. Sometimes that's exactly right — "list players who are on a team". But if the question is "list all players and their team, if any", you want a left join.

LEFT JOIN: keep everything on the left

SELECT p.player_id, p.first_name, p.last_name, t.team_name
FROM players AS p
LEFT JOIN squad AS s ON s.player_id = p.player_id
LEFT JOIN teams AS t ON t.team_id   = s.team_id;

"Left" simply means the table written first — players. Every player appears exactly once; the ones with no match get NULL in the columns that came from the other tables. That NULL is the correct value — his team is unknown/absent. Resist storing an empty string or a fake "no team" row instead (see NULL vs empty string). Aggregates such as COUNT(t.team_id) deliberately skip NULLs, so honest NULLs keep your counts honest.

For display, translate at query time with COALESCE, which returns the first non-NULL argument it's given:

SELECT p.first_name, p.last_name,
       COALESCE(t.team_name, 'Free agent') AS team_name
FROM players AS p
LEFT JOIN squad AS s ON s.player_id = p.player_id
LEFT JOIN teams AS t ON t.team_id   = s.team_id;

RIGHT JOIN: the mirror image

A RIGHT JOIN keeps every row from the table written second. "All teams, with their players if any" could be written either way:

-- RIGHT JOIN: teams is on the right, so every team survives
SELECT t.team_name, p.last_name
FROM players AS p
LEFT JOIN squad  AS s ON s.player_id = p.player_id
RIGHT JOIN teams AS t ON t.team_id   = s.team_id;

-- the same thing as a LEFT JOIN, just start from teams
SELECT t.team_name, p.last_name
FROM teams AS t
LEFT JOIN squad   AS s ON s.team_id   = t.team_id
LEFT JOIN players AS p ON p.player_id = s.player_id;

Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the tables — which is why nearly everyone writes queries left-to-right, puts the "keep all of these" table first, and never types RIGHT. It's not wrong; it's just one more thing for the next reader to mentally flip.

FULL OUTER JOIN (and why MySQL doesn't have it)

A full outer join keeps unmatched rows from both sides — every player and every team, with NULLs wherever there's no partner. PostgreSQL and SQL Server support FULL OUTER JOIN; MySQL doesn't have the keyword. Emulate it by gluing a left join to the rows the left join missed:

SELECT p.last_name, t.team_name
FROM players AS p
LEFT JOIN squad AS s ON s.player_id = p.player_id
LEFT JOIN teams AS t ON t.team_id   = s.team_id

UNION ALL

SELECT NULL, t.team_name           -- teams with no player: unmatched from the right
FROM teams AS t
LEFT JOIN squad AS s ON s.team_id = t.team_id
WHERE s.team_id IS NULL;

Rarely needed day-to-day — reconciliation reports ("what's in A but not B, and vice versa") are the classic use — but it's a common interview question and a common "why doesn't this parse?" surprise.

LEFT JOIN … WHERE … IS NULL: finding the rows with no match

Flip the left join into a filter and you get the "anti-join" — one of the most useful patterns in SQL. Players with no team:

SELECT p.player_id, p.first_name, p.last_name
FROM players AS p
LEFT JOIN squad AS s ON s.player_id = p.player_id
WHERE s.player_id IS NULL;

Every player is kept by the left join; the WHERE then keeps only those whose match came back empty. Test a column that can't be NULL in a real match (a key column) — never a nullable data column, or genuine matches with a missing value would sneak in. NOT EXISTS (SELECT 1 FROM squad WHERE ...) is the equally good alternative spelling.

Chaining several LEFT JOINs

Joins are evaluated left to right, and each one operates on the result so far. Two rules keep chains predictable:

• Once you go LEFT, stay LEFT. In the very first query above, if the second join were INNER JOIN teams, Henderson would be dropped again — the inner join discards his NULL team row. An inner join anywhere after a left join silently turns the whole chain back into an inner join for those rows.
• Filters on the right-hand table go in ON, not WHERE. "All players, and their team if it's Arsenal": LEFT JOIN teams t ON t.team_id = s.team_id AND t.team_name = 'Arsenal' keeps every player. Put t.team_name = 'Arsenal' in the WHERE instead and the NULL rows fail the test — you've accidentally written an inner join. This is the single most common left-join bug.

Always join with ON — never the comma

You'll still meet the ancient style where tables are listed with commas and the join conditions live in the WHERE clause:

-- works, but don't do this
SELECT p.first_name, t.team_name
FROM players p, squad s, teams t
WHERE p.player_id = s.player_id AND t.team_id = s.team_id;

Avoid it. With explicit JOIN ... ON, each condition sits next to the join it belongs to; with the comma style, join logic and filtering logic get tangled together, it's painfully easy to forget one condition and produce an accidental cartesian product, and there's no comma-style equivalent of a LEFT JOIN at all. One habit, formed early, saves you from a whole family of bugs.

Which one should I use? A one-line rule

Ask: "should a row from my main table disappear if the other table has nothing for it?" If yes — you only want the matches — INNER JOIN. If no — every row from the main table must survive — LEFT JOIN, with the main table written first. That question answers itself in nearly every real case; the other join types are refinements you'll reach for a few times a year.