➡️RIGHT JOIN

Mirror of LEFT: preserve the right side

RIGHT OUTER JOIN keeps every row from the right-hand table and drops left rows that have no matching partner (left columns become NULL for unmatched right rows). Relationally it is symmetric to LEFT JOIN: swap operand order and you get the same multiset of pairs, modulo column order in SELECT.

Many teams ban RIGHT JOIN in style guides so all queries “flow” left-to-right in the same mental model. That is reasonable, use it when reading vendor examples, then rewrite for consistency unless the RIGHT form genuinely reads clearer (rare).

SQL

RIGHT JOIN: every catalog row, offers if present

SELECT c.catalog_id, o.offer_id, o.discount_pct
FROM seasonal_offers o
RIGHT JOIN product_catalog c ON c.catalog_id = o.catalog_id
  AND o.season = 'SPRING';

✓Illustrative result set

catalog_id	offer_id	discount_pct
CAT-01	OFF-9	12
CAT-02	NULL	NULL
CAT-03	OFF-11	8

SQL

Equivalent LEFT JOIN (tables swapped)

SELECT c.catalog_id, o.offer_id, o.discount_pct
FROM product_catalog c
LEFT JOIN seasonal_offers o
  ON o.catalog_id = c.catalog_id
 AND o.season = 'SPRING';

✓Illustrative result set

catalog_id	offer_id	discount_pct
CAT-01	OFF-9	12
CAT-02	NULL	NULL
CAT-03	OFF-11	8

Mixing LEFT and RIGHT in one FROM list

Reviewers must simulate two different “driving” sides. Off-by-one mistakes in WHERE filters on padded NULLs become likely.

Pick one outer direction per query

Rewrite to a single driving side with CTEs: build the preserved grain first, then attach optional tables with LEFT JOIN only.

⚙️Optimizer note

Engines may internally rewrite RIGHT to LEFT.

The textual RIGHT keyword is for humans reading the SQL file; the execution plan does not care which side you wrote first. Choose whichever spelling your team will grep and review consistently.

When RIGHT shows up

Legacy reports, textbook snippets, or generated SQL from tools that emit a fixed table order. Treat them as LEFT joins after swapping operands during refactor.

Still watch WHERE

The same rule as LEFT applies: predicates on the nullable side that require non-NULL values belong in ON if you still need every right row.

⚡ Advanced / dialect note

Some BI layers generate RIGHT joins when users pick “all values from dimension B.” That is fine at generation time, persist the generated SQL as LEFT in git when your style guide requires it.

Teaching mnemonic

“The table name closest to JOIN is the inner side of the syntax tree in your head”: easy to misremember. Prefer explicit comments: /* preserve: product_catalog */ when you keep a non-obvious direction.

TL;DR

RIGHT OUTER JOIN is the mirror of LEFT JOIN: it preserves all rows from the right table and NULL-pads the left. It is logically redundant (swap tables and use LEFT) which is why many teams ban it for readability, use it only when it matches how you read a fixed query template from vendor docs.