Причины появления OUTER JOIN
Кстати, помнишь, мы объединяли наши таблицы и у нас пропали задачи по уборке офиса, так как самой уборщицы еще не было?
Если выполнить такой запрос:
SELECT * FROM task
То мы получим такой результат:
| id | employee_id | name | deadline |
|---|---|---|---|
| 1 | 1 | Исправить багу на фронтенде | 2022-06-01 |
| 2 | 2 | Исправить багу на бэкенде | 2022-06-15 |
| 3 | 5 | Купить кофе | 2022-07-01 |
| 4 | 5 | Купить кофе | 2022-08-01 |
| 5 | 5 | Купит кофе | 2022-09-01 |
| 6 | (NULL) | Убрать офис | (NULL) |
| 7 | 4 | Наслаждаться жизнью | (NULL) |
| 8 | 6 | Наслаждаться жизнью | (NULL) |
Задача “Убрать офис” пропадает, если мы попробуем объединить таблицу task с таблицей employee по employee_id.
Чтобы решить эту проблему, в оператор JOIN добавили различные модификаторы, которые позволяют сохранить такие потерянные строки без пары в другой таблице.
Напомню классический вид оператора JOIN:
таблица1 JOIN таблица2 ON
условие
Мы можем сказать SQL-серверу, чтобы все данные из левой таблицы (таблица1) обязательно присутствовали в объединенной таблице. Даже если для них нет пары в правой таблице. Для этого всего лишь нужно написать:
таблица1 LEFT JOIN таблица2 ON условиеЕсли же ты хочешь, чтобы в объединенной таблице обязательно были все строки из правой таблицы, то нужно написать:
таблица1 RIGHT JOIN таблица2 ON
условие Давай напишем запрос, который объединит все задачи и сотрудников, чтобы при этом задачи без исполнителя не терялись. Для этого нужно написать запрос:
SELECT * FROM employee e RIGHT JOIN task t ON e.id = t.employee_id
И результат такого запроса:
| id | name | occupation | salary | age | join_date | id | employee_id | name |
|---|---|---|---|---|---|---|---|---|
| 1 | Иванов Иван | Программист | 100000 | 25 | 2012-06-30 | 1 | 1 | Исправить багу на фронтенде |
| 2 | Петров Петр | Программист | 80000 | 23 | 2013-08-12 | 2 | 2 | Исправить багу на бэкенде |
| 4 | Рабинович Мойша | Директор | 200000 | 35 | 2015-05-12 | 7 | 4 | Наслаждаться жизнью |
| 5 | Кириенко Анастасия | Офис-менеджер | 40000 | 25 | 2015-10-10 | 3 | 5 | Купить кофе |
| 5 | Кириенко Анастасия | Офис-менеджер | 40000 | 25 | 2015-10-10 | 4 | 5 | Купить кофе |
| 5 | Кириенко Анастасия | Офис-менеджер | 40000 | 25 | 2015-10-10 | 5 | 5 | Купить кофе |
| 6 | Васька | кот | 1000 | 3 | 2018-11-11 | 8 | 6 | Наслаждаться жизнью |
| (NULL) | (NULL) | (NULL) | (NULL) | (NULL) | (NULL) | 6 | (NULL) | Убрать офис |
В нашу таблицу добавилась еще одна строка, и что интересно в ней очень много значений NULL. Все данные, которые брались из таблицы employee отображаются в виде NULL, так как для задачи “Убрать офис” не нашлось исполнителя из таблицы employee.
Типы JOIN-ов
Всего существует 4 типа JOIN-ов. Они представлены в таблице ниже:
| Краткая запись | Длинная запись | Пояснение | |
|---|---|---|---|
| 1 | JOIN | INNER JOIN | Только записи, которые есть в таблицах А и Б |
| 2 | LEFT JOIN | LEFT OUTER JOIN | Все строки без пары из таблицы А должны быть |
| 3 | RIGHT JOIN | RIGHT OUTER JOIN | Все строки без пары из таблицы Б должны быть |
| 4 | OUTER JOIN | FULL OUTER JOIN | Все строки баз пар из таблиц А и Б должны быть |
Для простоты, если мы представим таблицы в виде множеств, то JOIN можно будет отображать в виде картинки:
Под пересечением множеств подразумевается, что для одной таблицы есть соответствующая запись из другой таблицы, на которую она ссылается.
Вопрос с собеседования
Иногда программистов-новичков на собеседовании валят очень простым вопросом. С учетом наших таблиц его можно сформулировать так:
“Напишите запрос, которые отобразит список всех сотрудников, для которых нет задач”. Сначала попробуем немного перефразировать этот вопрос: “Напишите запрос, которые отобразит список всех сотрудников из таблицы employee, для которых нет задач в таблице task”. Нам нужно получить вот это множество:
Можно решить эту задачу многими способами, но я начну с самых простых: Во-первых, ты можешь объединить наши таблицы с помощью LEFT JOIN, а потом с помощью WHERE исключить все строки, для которых недостающие данные были дополнены NULL-ами.
SELECT * FROM employee e LEFT JOIN task t ON e.id = t.employee_id
WHERE t.id IS NULL
И результат такого запроса:
| id | name | occupation | salary | age | join_date | id | employee_id | name |
|---|---|---|---|---|---|---|---|---|
| 3 | Иванов Сергей | Тестировщик | 40000 | 30 | 2014-01-01 | (NULL) | (NULL) | (NULL) |
Единственный минус такого решения, что тут в таблице строки содержат NULL, а нам по условию нужно отобразить список сотрудников.
Для этого нужно или перечислить в SELECT требуемые колонки таблицы employee, или если нужно отобразить их все, то можно написать такую конструкцию:
SELECT e.* FROM employee e, task t
Полный запрос будет выглядеть так:
SELECT e.*
FROM employee e LEFT JOIN task t ON e.id = t.employee_id
WHERE t.id IS NULL
результат такого запроса:
| id | name | occupation | salary | age | join_date |
|---|---|---|---|---|---|
| 3 | Иванов Сергей | Тестировщик | 40000 | 30 | 2014-01-01 |
Остальные способы остаются вам для домашнего задания. Не хочу лишать вас удовольствия найти их самостоятельно.
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ