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

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

Дуже часто виникають ситуації, коли ми хочемо трохи підправити певну таблицю, і лише потім об'єднати (за допомогою оператора 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)
Java-університет

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

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

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< /span>, 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 Насолоджуватися життям

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

2. Використання оператора 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< /span>)
   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