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) |
Червоним кольором відзначений виправлений осередок.
Тепер давай підставимо нашу виправлену таблицю на запит:
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