Joining Multiple Tables & Choosing a Join

"Show each player with his team and the state his team's city is in." The state lives in a third table, cities. To get there you chain joins: players → teams → cities.

Definition

Joining multiple tables means adding one JOIN … ON … clause per extra table. SQL processes them left to right: the first two tables are joined, and the result is then joined to the next table, and so on.

• Each join needs its own key: players ↔ teams on team, teams ↔ cities on city.
• The join type (INNER / LEFT) is chosen separately for each step.
• Start from the main table (the one your question is about) and add the others one by one.

Syntax

SELECT a.col, b.col, c.col
FROM table_a AS a
LEFT JOIN table_b AS b
    ON a.key_ab = b.key_ab
LEFT JOIN table_c AS c
    ON b.key_bc = c.key_bc;

The third table

The setup creates cities (city, state) with 5 cities: Bengaluru–Karnataka, Mumbai–Maharashtra, Chennai–Tamil Nadu, Kolkata–West Bengal, Ahmedabad–Gujarat. Jaipur (RR) and Delhi (DC) are deliberately missing.

Example — INNER JOIN across three tables

SELECT p.name, t.team, c.city, c.state
FROM players AS p
INNER JOIN teams AS t
    ON p.team = t.team
INNER JOIN cities AS c
    ON t.city = c.city;

How it works:

1. Start with the main table: players (A).
2. JOIN teams (B) using the team key → 6 rows.
3. JOIN cities (C) using the city key → Buttler (Jaipur) and Warner (Delhi) find no city and are dropped.

INNER keeps only rows that match in every table — 4 of 6 players.

Example — LEFT JOIN across three tables

SELECT p.name, t.team, c.city, c.state
FROM players AS p
LEFT JOIN teams AS t
    ON p.team = t.team
LEFT JOIN cities AS c
    ON t.city = c.city;

LEFT keeps all players. Where no match was found, the cells are NULL.

Warning: Once you LEFT JOIN, keep using LEFT JOIN for the following tables. players LEFT JOIN teams INNER JOIN cities returns only 4 rows — the final INNER JOIN throws away the rows the LEFT JOIN kept, because their t.city has no match in cities.

Example — joining facts to dimensions

SELECT p.name, i.match_date, i.runs, t.team_name
FROM innings AS i
INNER JOIN players AS p
    ON i.player = p.name
INNER JOIN teams AS t
    ON p.team = t.team
WHERE i.runs >= 70
ORDER BY i.runs DESC;

How to choose the right join

Ask yourself: which rows do I want?

For multi-table queries:

1. Pick the main table — the one your question is about ("each player…" → players).
2. Decide if every row of it must stay → yes: LEFT JOIN the others; no (only complete matches): INNER JOIN.
3. Prefer LEFT over RIGHT; don't mix directions.

Common mistakes

• ❌ Joining C to A when the key is really in B (ON p.city = c.city — players has no city). ✅ Follow the real key path: players → teams → cities.
• ❌ LEFT JOIN followed by INNER JOIN on the optional table. ✅ Keep LEFT all the way.
• ❌ Not counting rows at each step. ✅ Run the query step by step and check the row count after each join.

Interview tip

Q: In A LEFT JOIN B LEFT JOIN C, can a row of A disappear? A: No — LEFT JOINs never remove rows of A (though a row of A can be repeated if B or C has several matches). Only an INNER join or a WHERE filter on B/C columns can remove it.