LEFT JOIN

A team analyst says: "Give me all players — and their team names if we have them." Losing a player just because his team row is missing would be a bug in the report. LEFT JOIN keeps every player no matter what.

Definition

LEFT JOIN (also written LEFT OUTER JOIN) returns all rows from the left table (the one after FROM), plus the matching rows from the right table. Where a left row has no match, the right table's columns are filled with NULL.

• Left table — the table written first (FROM players). It is the primary source; none of its rows are lost.
• Right table — the table after LEFT JOIN. It supplies extra data only where it matches.
• NULL fill — a missing partner is shown as NULLs, not dropped.
• In Venn terms: the whole left circle A, including the overlap.

Syntax

SELECT a.column_name, b.column_name
FROM table_a AS a              -- primary table: all rows kept
LEFT JOIN table_b AS b         -- extra data: only matches
    ON a.key_column = b.key_column;

Example — all players with their team (no orphans yet)

SELECT p.name, p.team, t.team AS t_team, t.team_name
FROM players AS p
LEFT JOIN teams AS t
    ON p.team = t.team;

In our seed data every player's team exists, so this gives the same 6 rows as the INNER JOIN. LEFT JOIN only looks different when the left table has unmatched rows — so let's create some.

Example — players whose team is missing

The playground setup adds Shubman Gill with team 'GT2' (a typo — no such team) and Ishan Kishan with team NULL:

INSERT INTO players (id, name, team, country, runs) VALUES
  (7, 'Shubman Gill', 'GT2', 'India', 600),
  (8, 'Ishan Kishan', NULL,  'India', 450);

SELECT p.name, p.team, t.team AS t_team, t.team_name
FROM players AS p
LEFT JOIN teams AS t
    ON p.team = t.team;

How it works: all 8 players are kept. Gill and Kishan found no team, so t_team and team_name come back as NULL. KKR and GT (teams without players) are not shown — they are on the right side, and LEFT JOIN only guarantees the left side.

Order of tables IS important

players LEFT JOIN teams → all players (8 rows here). teams LEFT JOIN players → all teams, including KKR and GT with NULL player columns.

SELECT t.team, t.team_name, p.name
FROM teams AS t
LEFT JOIN players AS p
    ON p.team = t.team;

Tip: Put the table you must not lose any rows from after FROM, then LEFT JOIN the lookup tables onto it.

ON vs WHERE in a LEFT JOIN (very important)

A condition on the right table behaves differently depending on where you put it:

Why? ON decides which right rows match (left rows are always kept). WHERE runs after the join and throws away any row where t.city is not 'Mumbai' — including the NULL rows the LEFT JOIN worked hard to keep.

Common mistakes

• ❌ Filtering the right table in WHERE and wondering why rows vanished. ✅ Put right-table filters in ON if you want to keep all left rows.
• ❌ Swapping tables by accident (teams LEFT JOIN players when you wanted all players). ✅ The "keep everything" table goes first.
• ❌ COUNT(*) per team after teams LEFT JOIN players — it counts the NULL row as 1 for KKR. ✅ Use COUNT(p.id), which ignores NULLs and correctly shows 0.

SQL Server vs MySQL / PostgreSQL

LEFT JOIN works the same everywhere. To replace NULLs in the output, SQL Server uses ISNULL(t.team_name, 'Unknown'); MySQL also has IFNULL; COALESCE works in all three.

Interview tip

Q: When does a LEFT JOIN return more rows than the left table has? A: When a left row matches more than one row in the right table — each match becomes its own row (e.g. teams LEFT JOIN players returns MI twice).