Оператор OUTER JOIN

1. Причини появи 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 .emploee_id
 

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

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

2. Типи JOIN-ів

Загалом існує 4 типи JOIN-ів. Вони представлені в таблиці нижче:

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

Під перетином множин мається на увазі, що для однієї таблиці є відповідний запис з іншої таблиці, на яку вона посилається.

3. Питання зі співбесіди

Іноді програмістів-новачків на співбесіді валять дуже простим питанням. З урахуванням наших таблиць його можна сформулювати так:

Можна вирішити це завдання багатьма способами, але я почну з найпростіших: По-перше, ти можеш об'єднати наші таблиці за допомогою LEFT JOIN, а потім за допомогою WHERE виключити всі рядки, для яких відсутні дані були доповнені NULL-ами.

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

Результат такого запиту:

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