Причины появления 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) обязательно присутствовали в объединенной таблице. Даже если для них нет пары в правой таблице. Для этого всего лишь нужно написать:

Java-университет 
таблица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

Остальные способы остаются вам для домашнего задания. Не хочу лишать вас удовольствия найти их самостоятельно.