Подзапрос возвращает таблицу
И наконец третий вариант – когда подзапрос возвращает целую таблицу. Это самый распространенный вариант.
Очень часто возникают ситуации, когда мы хотим немного подправить определенную таблицу. И только потом объединить (посредством оператора 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
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ