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 | |
| 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 | 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 | 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 | 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