RIGHT OUTER JOIN

A RIGHT OUTER JOIN walks every row of the right table and, for each one, attaches the matching left-table rows; when no left row satisfies the ON predicate it still emits the right row once, padding every left column with NULL — so the right table's row count is the floor of the result.

That one rule is the whole mechanism, and it exposes the fact worth memorizing: a RIGHT JOIN is just a LEFT JOIN with the table list reversed. A RIGHT JOIN B ON … returns exactly the same rows as B LEFT JOIN A ON …. Because almost everyone reads a query top-to-bottom and thinks "keep the table I named first," RIGHT JOIN is rarely used in production SQL — most teams normalize it away to LEFT JOIN in code review precisely so the preserved table is the one you read first. Knowing it exists matters for reading other people's queries and generated SQL; reaching for it yourself is usually a smell.

The two tables

We have students (the lookup side) and grades (the fact side). Note grade g5 references student_id = 5, who was deleted — a classic orphaned foreign-key row, the exact case an outer join is built to surface.

students                         grades
student_id | student_name        grade_id | student_id | grade
-----------+-------------        ---------+------------+------
    1      | Asha                  g1     |     1      |  A
    2      | Ben                   g2     |     2      |  B
    3      | Chen                  g3     |     1      |  A-
                                   g4     |     3      |  C
                                   g5     |     5      |  F   <- no student 5

We want every grade row, with the student name when one exists:

SELECT g.grade_id, g.student_id, s.student_name, g.grade
FROM   students s
RIGHT JOIN grades g
  ON   s.student_id = g.student_id
ORDER BY g.grade_id;

Step-by-step trace

The engine drives the loop from grades (the right, preserved table). For each grade row it probes students for rows where s.student_id = g.student_id:

1. g1 (student 1): probe finds Asha → emit (g1, 1, Asha, A).
2. g2 (student 2): finds Ben → emit (g2, 2, Ben, B).
3. g3 (student 1): finds Asha again → emit (g3, 1, Asha, A-).
4. g4 (student 3): finds Chen → emit (g4, 3, Chen, C).
5. g5 (student 5): probe finds nothing. RIGHT JOIN still emits the grade once, filling the left columns with NULL → emit (g5, 5, NULL, F).

Student Chen? She appears (via g4). But a student with no grade row would simply never be visited — the right table never asks about left rows that have no match. Final result, 5 rows, one per grade:

grade_id | student_id | student_name | grade
---------+------------+--------------+------
   g1    |     1      | Asha         |  A
   g2    |     2      | Ben          |  B
   g3    |     1      | Asha         |  A-
   g4    |     3      | Chen         |  C
   g5    |     5      | NULL         |  F

Pitfalls

• A WHERE on the left table silently turns it into an INNER JOIN. Add WHERE s.student_name LIKE 'A%' and the g5 row (where student_name is NULL) is discarded — NULL LIKE 'A%' is unknown, not true. You filtered away the very orphan rows the outer join existed to keep. Predicates that should preserve unmatched rows belong in the ON clause, not WHERE; reserve WHERE for the table you are preserving.
• Counting the wrong side. COUNT(*) over a RIGHT JOIN counts right-table rows including NULL-padded ones, but COUNT(s.student_id) skips the NULLs. Reviewers routinely misread which count they wrote and report inflated or deflated numbers.
• Mixing LEFT and RIGHT joins in one chain. A LEFT JOIN B … RIGHT JOIN C … binds left-to-right, so the RIGHT JOIN preserves C against the whole (A LEFT JOIN B) result, not against A alone. The outer-ness you thought you had on A can evaporate. Keep a multi-table query all-LEFT and order the tables so the preserved one leads.
• Assuming the right table is the one named second visually. In FROM students RIGHT JOIN grades, the preserved table is grades — the one named second. This inversion of the usual "first table wins" intuition is the single most common source of "why are my rows missing" RIGHT JOIN bugs.

Why you'd ever actually use it

The honest answer is: almost never by choice. Two narrow cases: (1) you are appending a join to an existing long query and the table you want to preserve is already on the right, so flipping to a LEFT JOIN would mean rewriting the FROM list; (2) you are reading machine-generated SQL (ORMs, BI tools, federated query engines) that emits RIGHT JOINs. The result is provably identical to the swapped LEFT JOIN, so there is never a correctness or performance reason to prefer it — only a readability one, and readability favors LEFT.

Takeaways

• RIGHT OUTER JOIN keeps every row of the table named second, NULL-padding the left columns when no match exists.
• A RIGHT JOIN B ≡ B LEFT JOIN A — identical results, so it carries no unique power; prefer LEFT JOIN so the preserved table reads first.
• Filter the preserved side with ON, never WHERE, or you silently demote the outer join to an inner one and lose the unmatched rows.
• It earns its keep mainly as something you must recognize in generated or inherited SQL, not something you reach for.

Sources: ISO/IEC 9075 (SQL standard, outer-join semantics); PostgreSQL documentation §7.2.1.1 "Joined Tables"; Use the Index, Luke! by Markus Winand (on ON-vs-WHERE filtering and join order); C. J. Date, SQL and Relational Theory (outer join as inner join plus NULL-padded unmatched rows). Re-authored and deepened for this guide — the prior version mirrored the LEFT JOIN page with sides swapped; this version traces the right-table-driven mechanism, adds the LEFT≡RIGHT equivalence, the WHERE-demotes-to-INNER trap, and join-chain ordering pitfalls.

🎯 STANDOUT elevation: Why / example / when-not / failure / panel / drills — RIGHT OUTER JOIN

Why this exists / the decision it encodes

RIGHT JOIN exists as the dual of LEFT JOIN: preserve every right-table row, NULL-pad the left on misses. It has no unique power — A RIGHT JOIN B ≡ B LEFT JOIN A. Teams rewrite to LEFT so the preserved table is named first; you learn RIGHT to read generated/inherited SQL and avoid WHERE traps.

Worked example with numbers or traced SQL/FD

students: 1 Asha, 2 Ben, 3 Chen
grades: g1→1 A, g2→2 B, g3→1 A-, g4→3 C, g5→5 F (orphan)
FROM students s RIGHT JOIN grades g ON s.student_id=g.student_id
Drive from grades: g5 emits (g5, 5, NULL, F)
≡ FROM grades g LEFT JOIN students s ON …
WHERE s.student_name LIKE 'A%' demotes outer→inner: g5 dropped (NULL LIKE → UNKNOWN)

When NOT / named alternative

Prefer LEFT JOIN with preserved table first in new code. Use RIGHT only when extending a long FROM list or reading machine SQL. Never mix LEFT and RIGHT chains without rewriting — binding order surprises you.

Failure mode / ops fingerprint / interview trap

Trap: filtering preserved-side columns in WHERE and losing the orphans the outer join was for. COUNT(*) vs COUNT(left.col) mismatch. Ops: reports that "mysteriously" drop unmatched fact rows after a "cleanup" WHERE was added.

Domain judgment (K11 theory-bridge / K12 concurrency / K13 query-judgment)

K13: outer join choice is about which entity's completeness you guarantee in the result; predicates placement is correctness, not style.

Hostile-panel drills (with model answers)

Q1. Prove RIGHT is redundant.
Model answer: A RIGHT JOIN B ON P returns the same multiset as B LEFT JOIN A ON P; only table order and keyword change.

Q2. Where must a predicate go to keep unmatched right rows?
Model answer: In the ON clause (or filter only the preserved side after understanding NULL semantics). WHERE on left columns removes NULL-padded unmatched rows.

Q3. What does COUNT(s.student_id) report on the RIGHT JOIN result?
Model answer: Non-NULL left keys only — orphan grade rows with NULL student_id are not counted, unlike COUNT(*).