FULL JOIN


📖 Описание

Полное внешнее соединение включает совпавшие пары, а затем строки без пары из обоих источников. Недостающая сторона дополняется NULL. FULL JOIN — сокращение FULL OUTER JOIN.


🧩 Синтаксис

SELECT a.column_name, b.column_name
FROM first_source a
FULL OUTER JOIN second_source b ON b.key_column = a.key_column;

Псевдонимы различают столбцы источников. В Oracle для псевдонима таблицы в этих примерах AS не используется. Условие соединения можно составить из нескольких сравнений через AND.


💻 Самостоятельный пример для Oracle

Пример использует учебные комнаты и занятия. Блок WITH создаёт данные только для одного запроса: таблицы в базе создавать не нужно. У Pottery два занятия; Print и Wood пустуют. Outdoor sketch не привязан к комнате (NULL), а Guest event содержит неизвестный room_id=99. Такая строка намеренно моделирует неполный импорт; внешний ключ здесь не определён.

ROOM_ID ROOM_NAME
41 Pottery
42 Print
43 Photo
44 Wood
LESSON_ID ROOM_ID LESSON_NAME
501 41 Clay basics
502 41 Glaze lab
503 43 Light study
504 NULL Outdoor sketch
505 99 Guest event
WITH rooms (room_id, room_name) AS (
  SELECT 41, 'Pottery' FROM dual
  UNION ALL SELECT 42, 'Print' FROM dual
  UNION ALL SELECT 43, 'Photo' FROM dual
  UNION ALL SELECT 44, 'Wood' FROM dual
),
lessons (lesson_id, room_id, lesson_name) AS (
  SELECT 501, 41, 'Clay basics' FROM dual
  UNION ALL SELECT 502, 41, 'Glaze lab' FROM dual
  UNION ALL SELECT 503, 43, 'Light study' FROM dual
  UNION ALL SELECT 504, NULL, 'Outdoor sketch' FROM dual
  UNION ALL SELECT 505, 99, 'Guest event' FROM dual
)
SELECT r.room_id, r.room_name, l.lesson_id, l.lesson_name
FROM rooms r
FULL OUTER JOIN lessons l ON l.room_id = r.room_id
ORDER BY r.room_id NULLS LAST, l.lesson_id;
ROOM_ID ROOM_NAME LESSON_ID LESSON_NAME
41 Pottery 501 Clay basics
41 Pottery 502 Glaze lab
42 Print NULL NULL
43 Photo 503 Light study
44 Wood NULL NULL
NULL NULL 504 Outdoor sketch
NULL NULL 505 Guest event

Видны занятые и пустые комнаты, а также непривязанные занятия. Всего семь строк: три совпавшие пары, две пустые комнаты и два занятия без комнаты.


Общий ключ для сверки

Для следующих запросов на этой странице повторите блок WITH из первого полного примера, заменяя только завершающий SELECT.

SELECT COALESCE(r.room_id, l.room_id) AS room_key,
       r.room_name, l.lesson_id,
       CASE
         WHEN r.room_id IS NULL THEN 'ONLY_LESSON'
         WHEN l.lesson_id IS NULL THEN 'ONLY_ROOM'
         ELSE 'MATCHED'
       END AS match_status
FROM rooms r
FULL OUTER JOIN lessons l ON l.room_id = r.room_id
ORDER BY room_key NULLS LAST, l.lesson_id;
ROOM_KEY ROOM_NAME LESSON_ID MATCH_STATUS
41 Pottery 501 MATCHED
41 Pottery 502 MATCHED
42 Print NULL ONLY_ROOM
43 Photo 503 MATCHED
44 Wood NULL ONLY_ROOM
99 NULL 505 ONLY_LESSON
NULL NULL 504 ONLY_LESSON

COALESCE выбирает первый непустой ключ. У занятия 504 нет ключа ни с одной стороны, поэтому room_key остаётся NULL. Для реальной сверки данных сравнивайте также нужные значения, а не только наличие ключа.

Дубликаты ключей

FULL JOIN не устраняет повторы. Если одному ключу соответствуют две строки слева и три справа, получится шесть пар. Сначала определите нужную детализацию или агрегируйте источники. UNION двух внешних соединений без продуманной обработки повторов может дать другой результат.


Практическое упражнение

Оставьте только строки без совпадения с любой стороны. Повторите WITH из первого примера.

Решение

SELECT r.room_id, r.room_name, l.lesson_id, l.lesson_name
FROM rooms r
FULL OUTER JOIN lessons l ON l.room_id = r.room_id
WHERE r.room_id IS NULL OR l.lesson_id IS NULL
ORDER BY r.room_id NULLS LAST, l.lesson_id;
ROOM_ID ROOM_NAME LESSON_ID LESSON_NAME
42 Print NULL NULL
44 Wood NULL NULL
NULL NULL 504 Outdoor sketch
NULL NULL 505 Guest event

Для определения отсутствующей стороны использованы идентификаторы, заполненные в исходных строках. Обычный фильтр одной стороны в WHERE может убрать нужные строки другой стороны.


Читайте также

Обзор JOIN · INNER JOIN · LEFT JOIN · RIGHT JOIN · EXISTS


Документация

Oracle: Joins · Oracle: SELECT