В отчёте нужно вывести сотрудника и название его отдела. Сотрудники хранятся в таблице employees, отделы — в departments. Если в отчёт должны попасть и сотрудники без отдела, и отделы без сотрудников, выбор соединения становится важной частью запроса.
В SQL эти случаи зависят от вида соединения. JOIN сопоставляет строки таблиц по условию, а INNER, LEFT, RIGHT и FULL определяют, какие строки сохранить, если пара не найдена.
На небольшом наборе данных разберём каждый вариант, сравним сами результирующие строки и увидим, почему перенос фильтра из ON в WHERE способен изменить результат. Для примеров нужны только базовые знания SELECT и WHERE.
Две таблицы: сотрудники и отделы
В таблице employees три сотрудника. Анна и Борис работают в разработке, Вера не привязана ни к одному отделу. Таблица departments содержит два отдела — разработку и аналитику; в аналитике сотрудников нет.
| employee_id | employee | department_id |
|---|---|---|
| 1 | Анна | 10 |
| 2 | Борис | 10 |
| 3 | Вера | NULL |
| department_id | department |
|---|---|
| 10 | Разработка |
| 20 | Аналитика |
Стрелки на схеме показывают две найденные связи по значению 10. У Веры ключ отдела равен NULL, а на отдел аналитики не ссылается ни один сотрудник.
Для работы с данными можно создать две таблицы и добавить записи:
CREATE TABLE departments (
department_id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department_id INTEGER REFERENCES departments(department_id)
);
INSERT INTO departments (department_id, name) VALUES
(10, 'Разработка'),
(20, 'Аналитика');
INSERT INTO employees (employee_id, name, department_id) VALUES
(1, 'Анна', 10),
(2, 'Борис', 10),
(3, 'Вера', NULL);employees.department_id ссылается на ключ справочника departments.department_id. Он допускает NULL: отсутствие отдела здесь допустимо. При этом departments.department_id — первичный ключ, то есть каждому идентификатору отдела соответствует не более одной записи справочника.
Мы используем SQLite. SQLite поддерживает RIGHT и FULL OUTER JOIN начиная с версии 3.39.0. Это указано в примечаниях к выпуску SQLite 3.39.0. Если ваша версия ниже, обновите движок или выполните примеры в СУБД с поддержкой этих соединений, например PostgreSQL.
Как JOIN сопоставляет строки
Рассмотрим условие:
ON e.department_id = d.department_idОно сравнивает department_id сотрудника с идентификатором отдела. При значении 10 находятся две пары: «Анна — Разработка» и «Борис — Разработка». У Веры значение NULL, а у аналитики нет сотрудников с идентификатором отдела 20.
NULL означает отсутствие известного значения. Обычное сравнение NULL = 10 не истинно, поэтому такая пара не возникает. Это не специальное правило JOIN: оно следует из логики сравнения с NULL.
Соединение строит одну строку на каждую найденную пару. Если отдел 10 связан с двумя сотрудниками, он участвует в двух строках результата. JOIN не объединяет сотрудников в одну запись отдела.
Для внешних соединений есть дополнительный шаг: когда исходная строка не нашла пары, её можно сохранить, заполнив столбцы отсутствующей стороны значениями NULL. Сравнение типов соединения ниже следует из семантики JOIN в PostgreSQL.
INNER JOIN: только найденные пары
INNER JOIN возвращает только те пары строк, для которых условие ON истинно. Сотрудник без отдела и отдел без сотрудников в результате не появятся. В SQLite и PostgreSQL запись JOIN без слова INNER означает то же соединение.
SELECT e.employee_id, e.name AS employee, d.department_id, d.name AS department
FROM employees AS e
INNER JOIN departments AS d
ON e.department_id = d.department_id
ORDER BY e.employee_id, d.department_id;| employee_id | employee | department_id | department |
|---|---|---|---|
| 1 | Анна | 10 | Разработка |
| 2 | Борис | 10 | Разработка |
Получилось две строки. Если вам нужны только сотрудники с существующим отделом — например, для списка назначенных сотрудников, — такой запрос отвечает задаче. При этом он намеренно не покажет Веру.
LEFT JOIN: сохраняем строки левой таблицы
LEFT JOIN сохраняет строки левой таблицы даже без подходящей строки справа. Здесь слева стоит employees, поэтому в ответ попадут все три сотрудника.
SELECT e.employee_id, e.name AS employee, d.department_id, d.name AS department
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
ORDER BY e.employee_id, d.department_id;| employee_id | employee | department_id | department |
|---|---|---|---|
| 1 | Анна | 10 | Разработка |
| 2 | Борис | 10 | Разработка |
| 3 | Вера | NULL | NULL |
Получилось три строки. Вера сохранена, а столбцы отдела для неё заполнены NULL. Отдел «Аналитика» не добавляется сам по себе: правая таблица не является сохраняемой стороной этого запроса.
LEFT JOIN подходит, когда список всех сотрудников нужен независимо от заполненности справочника.
RIGHT JOIN: сохраняем строки правой таблицы
RIGHT JOIN сохраняет строки правой таблицы даже без подходящей строки слева. Справа теперь стоит departments, поэтому результат должен включать и пустой отдел аналитики.
SELECT e.employee_id, e.name AS employee, d.department_id, d.name AS department
FROM employees AS e
RIGHT JOIN departments AS d
ON e.department_id = d.department_id
ORDER BY d.department_id, e.employee_id;| employee_id | employee | department_id | department |
|---|---|---|---|
| 1 | Анна | 10 | Разработка |
| 2 | Борис | 10 | Разработка |
NULL | NULL | 20 | Аналитика |
Здесь тоже три строки, но они отличаются от ответа LEFT JOIN: Вера отсутствует, зато отдел аналитики сохранён. Если нужны все отделы, в том числе пустые, это подходящий выбор.
Ту же связь можно записать через LEFT JOIN, поменяв таблицы местами (показан фрагмент FROM):
FROM departments AS d
LEFT JOIN employees AS e ON e.department_id = d.department_idПри выборе и сортировке одних и тех же столбцов обе записи дают одинаковые пары. Такая симметрия прямо следует из определений внешних соединений.
FULL JOIN: сохраняем несовпавшие строки обеих таблиц
FULL JOIN включает и совпавшие пары, и строки без пары с обеих сторон. В результате будут две найденные пары — Анна и Борис с разработкой — и две строки без пары: Вера без отдела и аналитика без сотрудника.
SELECT e.employee_id, e.name AS employee, d.department_id, d.name AS department
FROM employees AS e
FULL OUTER JOIN departments AS d
ON e.department_id = d.department_id
ORDER BY COALESCE(e.employee_id, 999), d.department_id;| employee_id | employee | department_id | department |
|---|---|---|---|
| 1 | Анна | 10 | Разработка |
| 2 | Борис | 10 | Разработка |
| 3 | Вера | NULL | NULL |
NULL | NULL | 20 | Аналитика |
Четыре строки охватывают все совпавшие и несовпавшие записи исходных таблиц. FULL JOIN и FULL OUTER JOIN — две записи одного вида соединения; необязательное слово OUTER описано в документации PostgreSQL.
Формула «сохранить обе стороны» не означает «вернуть сумму количества исходных строк». Совпавшие пары формируют общий результат, а одна исходная строка может иметь несколько пар.
Сравнение четырёх JOIN на одинаковых данных
Во всех запросах выше одна и та же пара таблиц и одно условие e.department_id = d.department_id. Меняется только правило сохранения строк без пары.
| Вид | Найденные пары | Вера без отдела | Аналитика без сотрудников | Всего строк |
|---|---|---|---|---|
INNER JOIN | 2 | — | — | 2 |
LEFT JOIN | 2 | ✓ | — | 3 |
RIGHT JOIN | 2 | — | ✓ | 3 |
FULL JOIN | 2 | ✓ | ✓ | 4 |
На одной схеме видны не только количества, но и конкретные строки, которые добавляет каждый вид соединения:
Галочка означает, что исходная строка без пары остаётся в результате. Для неё столбцы другой таблицы принимают NULL.
Эти числа — результат именно нашего набора данных. Если отдел разработки получит третьего сотрудника, количество совпавших пар увеличится и все четыре результата изменятся. Чтобы увидеть это, смотрите не только на название JOIN, но и на условие ON и кратность отношений между таблицами.
В локальной проверке на SQLite 3.46.1 все четыре выборки вернули ожидаемые пары и количества строк. Для другого набора данных или другой версии СУБД результаты необходимо проверить в соответствующем окружении.
ON и WHERE: почему LEFT JOIN иногда теряет строки
ON определяет, какие строки двух таблиц подходят друг другу. WHERE фильтрует строки уже сформированного результата. Для INNER JOIN перенос простого фильтра нередко сохраняет результат, но при внешних соединениях эти места не равнозначны.
Фильтр в WHERE применяется после OUTER JOIN и может удалить добавленные строки с NULL. Например, запрос ниже оставит только Анну и Бориса:
SELECT e.employee_id, e.name AS employee, d.department_id, d.name AS department
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.department_id
WHERE d.name = 'Разработка'
ORDER BY e.employee_id;После соединения у Веры значение d.name равно NULL; сравнение NULL = 'Разработка' не истинно, поэтому строка не проходит WHERE.
Если вы хотите показать всех сотрудников, но присоединить сведения справа только об отделе разработки, поставьте условие в ON:
SELECT e.employee_id, e.name AS employee, d.department_id, d.name AS department
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id AND d.name = 'Разработка'
ORDER BY e.employee_id;Теперь результат содержит и Веру — всего три строки. Совпавшим строкам соответствует разработка, а для Веры столбцы d.* остаются NULL. Порядок обработки ON и WHERE для OUTER JOIN описан в документации SQLite.
Чтобы найти сотрудников без соответствующего отдела, проверяйте ключ правой таблицы:
WHERE d.department_id IS NULLДля поиска отсутствующей пары проверяйте IS NULL по не допускающему NULL ключу другой таблицы. В нашем примере это d.department_id: он объявлен PRIMARY KEY. Так выбирается Вера. Условие WHERE e.department_id IS NULL проверяет другое: заполнен ли идентификатор отдела у самого сотрудника. Оно не найдёт сотрудника с ненулевой ссылкой на отсутствующий отдел, тогда как проверка d.department_id IS NULL найдёт отсутствие подходящей строки справа. Сравнение = NULL не подходит: оно даёт неизвестное логическое значение, а не TRUE. См. правила сравнений с NULL.
Как проверить и выбрать подходящий JOIN
Сначала сформулируйте, какие исходные записи должны сохраняться без пары. Если только записи с найденной связью — выбирайте INNER JOIN. Все сотрудники — LEFT JOIN от employees. Все отделы — RIGHT JOIN либо LEFT JOIN с переставленными таблицами. Обе стороны — FULL JOIN.
Для самостоятельной проверки выполните создание таблиц из примера, затем по очереди четыре запроса. Ожидаемые количества строк — 2, 3, 3 и 4; сверяйте также содержимое строк, а не только COUNT(*). При ошибке «неизвестный тип RIGHT/FULL» проверьте версию SQLite: нужна 3.39.0 или более новая. Если версия подходит, а запрос не работает, сначала убедитесь, что выполнены CREATE TABLE и INSERT, а имена и псевдонимы столбцов совпадают с листингом.
Без ORDER BY порядок возвращаемых строк не гарантируется. В примерах сортировка указана явно; это свойство описано в документации SQLite. Для диагностики важно сравнивать одинаковые наборы строк, не полагаясь на случайный порядок.
Наконец, вид JOIN не ограничивает число совпадений одной исходной строкой. Если позже соединять работников, задачи и другие таблицы со связями один-ко-многим, число строк может вырасти, а SUM — посчитать повторения. Это уже отдельная проблема агрегирования; здесь ключевой результат — умение определить, какие совпавшие и несовпавшие строки сохраняет каждый вид соединения.
Источники
- Определения INNER, LEFT, RIGHT, FULL JOIN; соответствие строк и NULL при отсутствии пары
- Синтаксис JOIN и применение условий ON и WHERE в запросе SELECT
- Правила NULL и проверка IS NULL вместо сравнения = NULL
- Работа JOIN, применение WHERE после OUTER JOIN, ORDER BY и результат без сортировки
- Появление поддержки RIGHT и FULL OUTER JOIN в SQLite 3.39.0