FULL OUTER JOIN: что вернётся при наличии совпадений

Условие

Что произойдёт, если выполнить запрос с FULL OUTER JOIN между двумя таблицами, в которых есть совпадающие записи?

Решение

FULL OUTER JOIN (он же FULL JOIN) возвращает:

• все совпадающие строки (как INNER JOIN),
• плюс строки слева без пары справа (с NULL в правых колонках),
• плюс строки справа без пары слева (с NULL в левых колонках).

То есть FULL JOIN = LEFT JOIN ∪ RIGHT JOIN.

Пример

-- a: id = 1, 2, 3
-- b: id = 2, 3, 4
 
SELECT a.id AS a_id, b.id AS b_id
FROM a
FULL OUTER JOIN b ON a.id = b.id;
 
-- a_id | b_id
-- 1    | NULL    ← из левой
-- 2    | 2       ← совпадение
-- 3    | 3       ← совпадение
-- NULL | 4       ← из правой

Где применяется

Поиск только различий

SELECT a.id AS only_in_a, b.id AS only_in_b
FROM a
FULL JOIN b ON a.id = b.id
WHERE a.id IS NULL OR b.id IS NULL;

Поддержка по СУБД

Подводные камни

1. COALESCE(a.id, b.id) — частый трюк, чтобы получить непустой ключ из обеих сторон.
2. WHERE после FULL JOIN — фильтрация по a.col отбросит «правые» строки (там a.col IS NULL). Это неожиданно превращает FULL в RIGHT.
3. MySQL до 8.0 — нет FULL JOIN, нужен UNION ALL двух частей.
4. Декартово произведение — если ON-условие неуникально, FULL JOIN взрывается.

Эталонный ответ

FULL OUTER JOIN вернёт все строки обеих таблиц: при отсутствии пары значения колонок недостающей стороны будут NULL.