RIGHT and FULL OUTER JOIN Semantics
When each is needed and how to rewrite RIGHT as LEFT.
Beyond LEFT JOIN
Once you understand LEFT JOIN, interviewers test the mirror image and the union of both: RIGHT JOIN and FULL OUTER JOIN.
- RIGHT JOIN preserves every row of the right table.
- FULL OUTER JOIN preserves unmatched rows from both tables.
This lesson defines each precisely and shows the rewrite trick interviewers love: any RIGHT JOIN can become a LEFT JOIN.
RIGHT JOIN Defined
A RIGHT JOIN (or RIGHT OUTER JOIN) keeps every row from the right table, the one written after the JOIN keyword. Unmatched right rows appear with NULL in the left-table columns.
It is the exact mirror of LEFT JOIN. Where LEFT preserves the first-named table, RIGHT preserves the second-named one.
SELECT c.name, o.amount
FROM orders o
RIGHT JOIN customers c
ON o.customer_id = c.id;
-- keeps ALL customers, even those
-- with no order (Carol -> amount NULL)All lessons in this course
- LEFT JOIN and Preserving Unmatched Rows
- RIGHT and FULL OUTER JOIN Semantics
- Finding Rows With No Match (Anti-Join)
- The WHERE-on-Outer-Join Trap