RIGHT JOIN

"List every IPL franchise and the players we have for it — even franchises with no players yet." Here the table you must not lose rows from is teams. If you've already written FROM players, a RIGHT JOIN keeps every team.

Definition

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

• It is the mirror image of LEFT JOIN: now the right table is the primary source.
• In Venn terms: the whole right circle B, including the overlap.
• Rows of the left table that have no partner are lost.

Syntax

SELECT a.column_name, b.column_name
FROM table_a AS a
RIGHT JOIN table_b AS b        -- all rows of table_b are kept
    ON a.key_column = b.key_column;

Example — all teams, with players where available

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

How it works: every row of teams (B) is kept. KKR and GT have no players, so the player columns (name, team) are NULL. Notice p.team is NULL but t.team shows the code — that's why we select both: t_team tells you which team had no partner.

Order of tables IS important — players without a team are lost

If the setup adds Shubman Gill (team 'GT2') and Ishan Kishan (team NULL), the RIGHT JOIN above still returns the same 8 rows: Gill and Kishan are in the left table and have no matching team, so RIGHT JOIN drops them.

Alternative to RIGHT JOIN — swap the tables and use LEFT JOIN

Same results! Both keep all teams and add players where they match. Only the default column order of SELECT * changes (players' columns first vs teams' columns first) — if you list columns explicitly, the output is identical.

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

Tip: Most teams and style guides prefer LEFT JOIN only. Reading every query as "main table first, then extras" is easier than switching direction in your head. Learn RIGHT JOIN so you can read other people's code, but write LEFT JOIN.

Common mistakes

• ❌ Expecting RIGHT JOIN to keep all rows of the table after FROM. ✅ It keeps all rows of the table after RIGHT JOIN.
• ❌ Mixing LEFT and RIGHT in a multi-table query — very hard to reason about. ✅ Rewrite everything as LEFT JOINs from one main table.
• ❌ Reading the key from the left table in the output (p.team) — it's NULL for unmatched rows. ✅ Show the right table's key (t.team).

SQL Server vs MySQL / PostgreSQL

RIGHT JOIN is supported by SQL Server, MySQL, PostgreSQL and (since version 3.39) SQLite. Older SQLite versions did not support it — another reason the swap-to-LEFT trick is worth knowing.

Interview tip

Q: Can every RIGHT JOIN be written as a LEFT JOIN? A: Yes. A RIGHT JOIN B ON … is exactly B LEFT JOIN A ON … — just swap the table order.