JOIN — соединения таблиц


📖 Что делает JOIN

JOIN сопоставляет строки двух источников и формирует из них общий результат. Источниками могут быть таблицы, представления или подзапросы. Условие ON определяет подходящие пары; тип соединения определяет, что делать со строками без пары.


Как выбрать соединение

Вид Что сохраняется Пример задачи
INNER JOIN (или JOIN) Только совпавшие пары Занятия с известной комнатой
LEFT JOIN Все строки слева и найденные пары Все комнаты, включая пустые
RIGHT JOIN Все строки справа и найденные пары Все занятия, включая непривязанные
FULL JOIN Все совпавшие пары и несовпавшие строки обеих сторон Сверка двух списков
CROSS JOIN Все возможные пары Комбинации комнаты и времени
SELF JOIN Соединение источника с самим собой Сотрудник и его руководитель

💻 Общий набор данных

Пример использует учебные комнаты и занятия. Блок 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
INNER 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
43 Photo 503 Light study

В INNER JOIN участвуют только занятия 501, 502 и 503. Сокращённая запись JOIN означает INNER JOIN, а не выбор вида соединения по наличию внешнего ключа.

Сколько строк вернут разные виды

СОЕДИНЕНИЕ СТРОК
INNER JOIN 3
LEFT JOIN 5
RIGHT JOIN 5
FULL JOIN 7
CROSS JOIN 20

Четыре комнаты и пять занятий дают 4 × 5 = 20 пар при CROSS JOIN. При соединении по ключу Pottery повторяется для каждого своего занятия: связь один-ко-многим не должна превращаться в одну строку автоматически.


CROSS JOIN: все комбинации

WITH rooms (room_name) AS (
  SELECT 'Pottery' FROM dual
  UNION ALL SELECT 'Photo' FROM dual
), slots (start_hour) AS (
  SELECT 10 FROM dual
  UNION ALL SELECT 14 FROM dual
)
SELECT r.room_name, s.start_hour
FROM rooms r CROSS JOIN slots s
ORDER BY r.room_name, s.start_hour;
ROOM_NAME START_HOUR
Photo 10
Photo 14
Pottery 10
Pottery 14

SELF JOIN: один источник, две роли

SELF JOIN — название приёма, отдельного ключевого слова SELF в запросе нет. Здесь LEFT JOIN сохраняет руководителя верхнего уровня, у которого нет собственного руководителя:

WITH mentors (mentor_id, mentor_name, manager_id) AS (
  SELECT 61, 'Malika', CAST(NULL AS NUMBER) FROM dual
  UNION ALL SELECT 62, 'Rustam', 61 FROM dual
  UNION ALL SELECT 63, 'Dilorom', 61 FROM dual
)
SELECT e.mentor_name, m.mentor_name AS manager_name
FROM mentors e
LEFT JOIN mentors m ON m.mentor_id = e.manager_id
ORDER BY e.mentor_id;
MENTOR_NAME MANAGER_NAME
Malika NULL
Rustam Malika
Dilorom Malika

Типичные ошибки

  • Условие ON пропущено или слишком широкое — строк становится больше ожидаемого.
  • NULL = NULL не даёт TRUE: для проверки отсутствия значения используется IS NULL.
  • Фильтр правой таблицы в WHERE после LEFT JOIN может убрать строки без совпадений.
  • JOIN соединяет столбцы и пары строк; UNION объединяет совместимые результаты по вертикали.

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

Как получить комнаты, в которых нет ни одного занятия? Выберите вид соединения и условие.

Решение

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
LEFT JOIN lessons l ON l.room_id = r.room_id
WHERE 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

lesson_id заполнен у каждой исходной записи занятия, поэтому NULL после соединения показывает отсутствие пары. Ещё один вариант — NOT EXISTS.


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

INNER JOIN · LEFT JOIN · RIGHT JOIN · FULL JOIN


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

Oracle: Joins