RIGHT and FULL OUTER JOIN
RIGHT JOIN mirrors LEFT JOIN, and because MySQL 8.4 has no FULL OUTER JOIN you emulate one with UNION ALL.
SELECT ... FROM a LEFT JOIN b ON a.k=b.k UNION ALL SELECT ... FROM a RIGHT JOIN b ON a.k=b.k WHERE a.k IS NULLRIGHT JOIN is LEFT JOIN with the operands swapped: it preserves every row of the table on its right and NULL-fills the left-hand columns where the ON condition finds nothing. RIGHT JOIN and RIGHT OUTER JOIN are the same statement, OUTER being optional.
Prefer LEFT
The MySQL manual recommends writing right joins as left joins for portability. Any a RIGHT JOIN b ON p is b LEFT JOIN a ON p — identical rows, only the column order of SELECT * differs. The practical argument is readability: in a chain of four tables mixing left and right joins, nobody can tell at a glance which side is allowed to be NULL. Keep every outer join pointing the same direction.
MySQL has no FULL OUTER JOIN
Through 8.4 there is no FULL OUTER JOIN keyword; writing one is a syntax error. Emulate it as a union of two halves: all rows of the left table with their matches, then only the right-hand rows that the first half could not produce. The second half needs an IS NULL guard on the left-hand key so matched pairs are not emitted twice.
Reading the result
Both branches must return the same number of columns, in the same order, with compatible types. The join key is NULL on whichever side is absent, so select COALESCE(a.sku, b.sku) as the merged key and group by that — not by either table's column alone.
Example
-- Reconciliation: what is in the warehouse, what has been sold, and
-- every SKU that appears in only one of the two.
-- MySQL 8.4 has no FULL OUTER JOIN, so this is the standard rewrite.
-- Half one: every warehouse row, with sales where they exist.
SELECT w.sku AS sku,
w.on_hand,
s.units_sold
FROM warehouse_stock AS w
LEFT JOIN sales_totals AS s ON s.sku = w.sku
UNION ALL
-- Half two: only the sales rows half one could not produce.
-- Without the IS NULL guard, matched SKUs would appear twice.
SELECT s.sku,
NULL AS on_hand,
s.units_sold
FROM warehouse_stock AS w
RIGHT JOIN sales_totals AS s ON s.sku = w.sku
WHERE w.sku IS NULL
ORDER BY sku;When to use it
- Reconcile two systems where each may hold records the other does not — stock versus sales, budget versus actuals.
- Rewrite an inherited RIGHT JOIN into the LEFT JOIN form the rest of the codebase uses.
- Port a FULL OUTER JOIN query from PostgreSQL or SQL Server to MySQL 8.4.
- Build a complete key list from several tables and attach each table's columns to it.
More examples
The same query written both ways
Learn to convert on sight: swap the two table references and change the keyword, leaving the ON predicate untouched. You will meet RIGHT JOIN in inherited queries and in code translated from other dialects, and converting it is usually the fastest way to work out what the query actually preserves.
-- RIGHT: preserves every row of customers, the table on the right.
SELECT o.id, c.email
FROM orders AS o
RIGHT JOIN customers AS c ON c.id = o.customer_id;
-- LEFT: identical result set, operands swapped. This is the form
-- to write; the manual recommends it for portability.
SELECT o.id, c.email
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;A merged key column, not one side's key
The full-join emulation written with two LEFT JOINs instead of a LEFT and a RIGHT — equivalent, and easier to read because both halves point the same way. COALESCE on the key matters: selecting b.month alone would give NULL for every actuals-only month, and ORDER BY would then sort those rows to the top.
-- Month-by-month budget versus actuals, where a month may exist in
-- one table and not the other.
SELECT COALESCE(b.month, a.month) AS month, -- never NULL
b.budget_cents,
a.actual_cents
FROM budgets AS b
LEFT JOIN actuals AS a ON a.month = b.month
UNION ALL
SELECT a.month,
NULL,
a.actual_cents
FROM actuals AS a
LEFT JOIN budgets AS b ON b.month = a.month
WHERE b.month IS NULL
ORDER BY month;When UNION ALL and IS NULL are not needed
A cleaner full-join emulation for wide rows: build the union of keys once, then hang an outer join off it per table. It scales to three or more sources, where the two-branch UNION ALL pattern does not, and it also demonstrates that UNION is shorthand for UNION DISTINCT.
-- If you only need the key list -- the set of SKUs known to either
-- side -- the deduplicating UNION is the right tool and is shorter.
SELECT sku FROM warehouse_stock
UNION -- UNION is shorthand for UNION DISTINCT
SELECT sku FROM sales_totals;
-- That set can then drive a pair of LEFT JOINs, which is often
-- clearer than a two-branch union of wide rows.
WITH all_skus AS (
SELECT sku FROM warehouse_stock
UNION
SELECT sku FROM sales_totals
)
SELECT k.sku, w.on_hand, s.units_sold
FROM all_skus AS k
LEFT JOIN warehouse_stock AS w ON w.sku = k.sku
LEFT JOIN sales_totals AS s ON s.sku = k.sku;
Discussion