FULL OUTER JOIN keeps every row from both tables — matched pairs, unmatched left rows (with NULLs on the right), and unmatched right rows (with NULLs on the left).

-- PostgreSQL, SQLite 3.39+, BigQuery, Snowflake, DuckDB
SELECT c.id AS customer_id, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

MySQL has no FULL OUTER JOIN, in any version. Emulate it: a LEFT JOIN covers matched rows and left-only rows; UNION ALL a RIGHT JOIN filtered down to just the right-only rows to add what the LEFT JOIN couldn’t see.

-- MySQL: LEFT JOIN (matched + left-only) UNION ALL RIGHT JOIN anti-joined to right-only
SELECT c.id AS customer_id, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id

UNION ALL

SELECT c.id AS customer_id, o.id AS order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id
WHERE c.id IS NULL;

Dialect notes:

PostgreSQL, BigQuery, Snowflake, DuckDB: FULL OUTER JOIN (or just FULL JOIN — same thing).
SQLite: FULL OUTER JOIN and RIGHT JOIN were only added in 3.39.0 (2022). On an older SQLite, use the MySQL-style emulation below too.
MySQL: no FULL OUTER JOIN in any released version — always emulate.

Why WHERE c.id IS NULL and not a plain UNION of two LEFT/RIGHT joins. A simpler-looking emulation — LEFT JOIN ... UNION LEFT JOIN swapped — relies on UNION’s implicit de-duplication to collapse the matched rows that both halves produce, which forces a sort/hash pass over the whole result. Anti-joining the second half with WHERE c.id IS NULL (matched rows already came from the LEFT JOIN, so this is left-side-missing = right-only) means each row is produced exactly once, so UNION ALL is correct and cheaper than UNION.

FULL OUTER JOIN (and emulating it in MySQL)