Причини появи OUTER JOIN

До речі, пам'ятаєш, ми об'єднували наші таблиці і в нас зникли завдання з прибирання офісу, оскільки самої прибиральниці ще не було?

Якщо виконати такий запит:

SELECT * FROM task

То ми отримаємо такий результат:

id emploee_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.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.

Типи 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.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 RIGHT 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

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

Вебінар JavaRush