LEFT JOIN
📖 Описание
Левое внешнее соединение сохраняет каждую строку слева. При отсутствии пары столбцы правого источника получают NULL. LEFT JOIN и LEFT OUTER JOIN — одинаковые формы записи.
🧩 Синтаксис
SELECT a.column_name, b.column_name
FROM first_source a
LEFT 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
LEFT 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 |
Print и Wood присутствуют с пустыми столбцами занятия. У Pottery две строки. Занятия с неизвестной комнатой не сохраняются, потому что находятся справа.
Фильтр в ON и фильтр в WHERE
Для следующих запросов на этой странице повторите блок WITH из первого полного примера, заменяя только завершающий SELECT.
SELECT r.room_id, r.room_name, l.lesson_id
FROM rooms r
LEFT JOIN lessons l
ON l.room_id = r.room_id AND l.lesson_id >= 502
ORDER BY r.room_id, l.lesson_id;
| ROOM_ID | ROOM_NAME | LESSON_ID |
|---|---|---|
| 41 | Pottery | 502 |
| 42 | NULL | |
| 43 | Photo | 503 |
| 44 | Wood | NULL |
Фильтр в ON ограничивает подходящие занятия, сохраняя все комнаты. Если перенести l.lesson_id >= 502 в WHERE, строки Print и Wood будут отброшены: сравнение с NULL не истинно. Останутся только комнаты 41 и 43.
Комнаты без занятий
SELECT r.room_id, r.room_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;
| ROOM_ID | ROOM_NAME |
|---|---|
| 42 | |
| 44 | Wood |
Проверяйте столбец правой стороны, который не бывает NULL в реальных строках. nullable-описание занятия для этой цели не подходит.
Практическое упражнение
Выведите количество занятий во всех комнатах, включая ноль. Повторите WITH из первого примера.
Решение
SELECT r.room_id, r.room_name, COUNT(l.lesson_id) AS lesson_count
FROM rooms r
LEFT JOIN lessons l ON l.room_id = r.room_id
GROUP BY r.room_id, r.room_name
ORDER BY r.room_id;
| ROOM_ID | ROOM_NAME | LESSON_COUNT |
|---|---|---|
| 41 | Pottery | 2 |
| 42 | 0 | |
| 43 | Photo | 1 |
| 44 | Wood | 0 |
COUNT(l.lesson_id) игнорирует NULL. COUNT(*) посчитал бы одну дополненную строку и ошибочно дал бы 1 для пустых комнат.
Читайте также
Обзор JOIN · INNER JOIN · RIGHT JOIN · FULL JOIN · EXISTS