FULL JOIN

A data auditor asks: "Show me every player and every team in one list — matched where possible, and make the mismatches visible on both sides." That is a FULL JOIN.

Definition

FULL JOIN (also FULL OUTER JOIN) returns all rows from both tables. Matching rows are combined into one row; a row from either table without a partner is still returned, with NULL in the other table's columns.

• All rows from both — nothing is lost from A or B. In Venn terms: both circles completely.
• Matched rows appear once, combined (like INNER JOIN).
• Left-only rows have NULLs on the right; right-only rows have NULLs on the left.
• Think of it as LEFT JOIN + RIGHT JOIN together, without duplicating the matches.

Syntax

SELECT a.column_name, b.column_name
FROM table_a AS a
FULL JOIN table_b AS b          -- or FULL OUTER JOIN
    ON a.key_column = b.key_column;

Example — everything from players and teams

The setup adds two orphan players — Shubman Gill (team 'GT2', a typo) and Ishan Kishan (team NULL) — so both sides have unmatched rows:

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

How it works:

• Rows 1–6: matched pairs (the overlap).
• Rows 7–8: players with no team (left circle only) → team columns NULL.
• Rows 9–10: teams with no players (right circle only) → player columns NULL.

Without the setup rows (plain seed data) every player matches, so the FULL JOIN returns 8 rows: the 6 matches plus KKR and GT.

Order of tables does NOT matter

players FULL JOIN teams and teams FULL JOIN players contain the same rows — both circles are kept completely either way.

Comparison of the four basic joins

Making the output readable

NULLs in a report look ugly. Replace them:

SELECT COALESCE(p.name, '(no player)') AS player,
       COALESCE(t.team_name, '(unknown team)') AS team_name
FROM players AS p
FULL JOIN teams AS t
    ON p.team = t.team;

In SQL Server you can also use ISNULL(p.name, '(no player)').

Common mistakes

• ❌ Using FULL JOIN when you really need only one side — it produces extra NULL rows to filter out later. ✅ Choose the join that matches your question.
• ❌ Filtering a FULL JOIN in WHERE on one table's column (e.g. WHERE p.country = 'India') — it silently drops all right-only rows. ✅ Filter carefully or in ON.
• ❌ Assuming the matched/unmatched rows come out in a particular order. ✅ Add ORDER BY if order matters.

SQL Server vs MySQL / PostgreSQL

Interview tip

Q: How would you do a FULL OUTER JOIN in MySQL? A: Combine a LEFT JOIN and a RIGHT JOIN with UNION: SELECT … FROM a LEFT JOIN b ON … UNION SELECT … FROM a RIGHT JOIN b ON …. UNION removes the duplicated matched rows.