Підзапит повертає таблицю

І нарешті третій варіант – коли підзапит повертає цілу таблицю. Це найпоширеніший варіант.

Дуже часто виникають ситуації, коли хочемо трохи підправити певну таблицю. І лише потім об'єднати (за допомогою оператора JOIN ON) виправлену таблицю з іншого.

Давай спочатку візьме найпростіший випадок, де ми об'єднували дві таблиці за допомогою JOIN:

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

І як ти, напевно, пам'ятаєш, у таблиці task є завдання, які ні на кого не призначені: employee_id і NULL .

Давай згенеруємо виправлену таблицю , де всі задачі, що повисли, призначимо на директора (його ID = 4).

Для цього скористаємося функцією IFNULL() :

SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline 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 4 Прибрати офіс (NULL)
7 4 Насолоджуватися життям (NULL)
8 6 Насолоджуватися життям (NULL)

Червоним кольором відзначений виправлений осередок.

Тепер давай підставимо нашу виправлену таблицю на запит:

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

Замість таблиці task .

Виглядати такий запит буде приблизно так:

SELECT * FROM employee e JOIN ( 
 	SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline
 	FROM task 
) t ON e.id = t.emploee_id

Ми замість слова task написали круглі дужки та в них помістабо тіло запиту.

До речі, псевдонім t (аліас) для вкладеного запиту дуже став у нагоді. У вкладеного запиту на відміну від таблиці свого імені немає, тому псевдонім дуже навіть до місця.

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

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 6 4 Прибрати офіс
4 Рабінович Мойша Директор 200000 35 2015-05-12 7 4 Насолоджуватися життям
5 Кирієнко Анастасія Офіс менеджер 40000 25 2015-10-10 4 5 Купити каву
5 Кирієнко Анастасія Офіс менеджер 40000 25 2015-10-10 5 5 Купити каву
5 Кирієнко Анастасія Офіс менеджер 40000 25 2015-10-10 3 5 Купити каву
6 Васько Кіт 1000 3 2018-11-11 8 6 Насолоджуватися життям

На нашому директорі висить завдання "прибрати офіс", думаю, він швидко знайде, кому її делегувати :) Використання оператора WITH

До речі, починаючи з 8-ї версії MySQL ти більше не зобов'язаний засовувати всі свої запити прямо всередину фінального запиту. Їх можна виконувати окремо. Для цього використовується оператор WITH .

Він дозволяє створити віртуальну таблицю (займенований запит) і його вид задається шаблоном:

WITH ім'я AS (запит)

Часто бувають ситуації, коли у вашому підзапиті є колонки без імені, наприклад, такі як COUNT(*), яким ти не призначив унікальне ім'я. На цей випадок оператор WITH має можливість вказати нові імена колонок для підзапиту.

Другий вид задається шаблоном:

WITH ім'я(колонка1, колонка2, …) AS (запит)

Ти можеш використовувати скільки завгодно віртуальних таблиць (займенованих запитів) і посилатись у них один на одного. Загальний вигляд твого запиту матиме приблизно такий формат:

WITH ім'я1 AS (запит1),  
       	ім'я2 AS (запит2), 
       	ім'я3 AS (запит3) 
SELECT * FROM ім'я1 JOIN ім'я2 ON …

Тепер давай візьмемо наш страшний запит:

SELECT * FROM employee e JOIN ( 
 	SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline 
 	FROM task 
) t ON e.id = t.emploee_id

І перепишемо його з використанням оператора WITH:

WITH task2(id, employee_id, name, deadline) 
   AS (SELECT id, IFNULL(employee_id, 4), name, deadline FROM task) 
SELECT * FROM employee e JOIN task2 t ON e.id = t.emploee_id

Або можна без імен колонок, але тоді доведеться обов'язково вказувати аліас для функції IFNULL():

WITH task2 AS ( 
 	SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline FROM task 
) 
SELECT * FROM employee e JOIN task2 t ON e.id = t.emploee_id
Вебінар JavaRush