1. Причини появи 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) обов'язково були присутні в об'єднаній таблиці. Навіть якщо для них немає пари в правій таблиці. Для цього лише потрібно написати:

Telegram 

таблиця1 LEFT JOIN таблиця2 ON умова

Якщо ти хочеш, щоб в об'єднаній таблиці обов'язково були всі рядки з правої таблиці, то потрібно написати: 


таблиця1 RIGHT JOIN таблиця2 ON
 умова

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


 
  SELECT * FROM  employee e RIGHT JOIN task t ON e.id = t .emploee_id

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

id name occupation salary age join_date id emploee_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.

2. Типи 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 можна буде відображати у вигляді картинки:

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

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

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

“Напишіть запит, який відображає список усіх працівників, для яких немає завдань”. Спочатку спробуємо трохи перефразувати це питання: “Напишіть запит, який відображає список усіх співробітників з таблиці employee, для яких немає завдань у таблиці task”. Нам потрібно отримати ось цю множину:

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

SELECT * FROM  employee e LEFT JOIN task t ON e.id = t .emploee_id
WHERE t.id IS NULL

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

id name occupation salary age join_date id emploee_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.emploee_id
WHERE t.id IS NULL

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

id name occupation salary age join_date
3 Шевченко Данило Тестувальник 40000 30 2014-01-01

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