INNER JOIN

"Show me every player together with his team's full name." Only players whose team really exists in the teams table can be shown with a team name — that is exactly what INNER JOIN does.

Definition

INNER JOIN returns only the rows where the join condition is true in both tables. Rows from either table that have no matching partner are discarded.

• Only matching rows — in Venn terms, just the overlap of circle A and circle B.
• Both tables — a row survives only if it finds at least one partner on the other side.
• Discarded — unmatched rows simply disappear; they don't show up with NULLs (that's what outer joins are for).
• If one row matches several rows on the other side, you get one result row per match (MI appears twice below because MI has two players).

Syntax

SELECT a.column_name, b.column_name
FROM table_a AS a
INNER JOIN table_b AS b
    ON a.key_column = b.key_column;

Example — players with their team names

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

How it works:

1. FROM players — take each player row.
2. INNER JOIN teams ON p.team = t.team — find the team row(s) with the same code.
3. KKR and GT have no players, so they find no partner and are dropped.
4. SELECT — pick the columns to show.

So 7 teams and 6 players produce 6 rows: every player matched, the 2 lonely teams vanished.

Order of tables does NOT matter

players INNER JOIN teams and teams INNER JOIN players return the same rows (only the column order you write in SELECT changes, and the display order of rows is not guaranteed without ORDER BY). The overlap of A and B is the same as the overlap of B and A.

Example — INNER JOIN as an existence check + filter

SELECT p.name, p.runs, t.city
FROM players AS p
INNER JOIN teams AS t
    ON p.team = t.team
WHERE t.city = 'Mumbai';

Execution order: FROM + JOIN ... ON build the combined rows → WHERE filters them → SELECT picks columns. You can filter on columns from either table after joining.

Example — what happens to rows without a partner

To see INNER JOIN drop players, we need players whose team is not in teams. The playground examples below add two such players in their setup:

Running the same INNER JOIN still returns only the 6 original players. Gill and Kishan are discarded because 'GT2' matches nothing and NULL never equals anything — not even another NULL.

Common mistakes

• ❌ Expecting INNER JOIN to keep everyone from the first table. ✅ It keeps only matched rows; use LEFT JOIN to keep all of A.
• ❌ Forgetting ON → SQL Server error "Incorrect syntax near ';'". ✅ Every INNER/LEFT/RIGHT/FULL JOIN needs an ON condition.
• ❌ Joining on a non-unique column and being surprised by duplicate rows. ✅ Know which side is "one" and which is "many" (one team → many players).
• ❌ Old comma style FROM players, teams WHERE p.team = t.team. ✅ It works, but forgetting the WHERE silently becomes a CROSS JOIN. Prefer explicit INNER JOIN ... ON.

Interview tip

Q: If table A has 6 rows and table B has 7, how many rows can an INNER JOIN return? A: Anywhere from 0 (no matches) upward — it is not capped at 6 or 7. If keys repeat on both sides, each match produces a row, so it can be as large as 6 × 7 = 42 in the extreme case.