Оператор OUTER JOIN

Причины появления OUTER JOIN

Кстати, помнишь, мы объединяли наши таблицы и у нас пропали задачи по уборке офиса, так как самой уборщицы еще не было?

Если выполнить такой запрос:

SELECT * FROM task

То мы получим такой результат:

Задача “Убрать офис” пропадает, если мы попробуем объединить таблицу 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

И результат такого запроса:

В нашу таблицу добавилась еще одна строка, и что интересно в ней очень много значений NULL. Все данные, которые брались из таблицы employee отображаются в виде NULL, так как для задачи “Убрать офис” не нашлось исполнителя из таблицы employee.

Типы JOIN-ов

Всего существует 4 типа JOIN-ов. Они представлены в таблице ниже:

Для простоты, если мы представим таблицы в виде множеств, то JOIN можно будет отображать в виде картинки:

Под пересечением множеств подразумевается, что для одной таблицы есть соответствующая запись из другой таблицы, на которую она ссылается.

Вопрос с собеседования

Иногда программистов-новичков на собеседовании валят очень простым вопросом. С учетом наших таблиц его можно сформулировать так:

Можно решить эту задачу многими способами, но я начну с самых простых: Во-первых, ты можешь объединить наши таблицы с помощью LEFT JOIN, а потом с помощью WHERE исключить все строки, для которых недостающие данные были дополнены NULL-ами.

SELECT * FROM employee e LEFT JOIN task t ON e.id = t.employee_id WHERE t.id IS 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 

результат такого запроса:

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