Подзапросы с таблицами

Подзапрос возвращает таблицу

И наконец третий вариант – когда подзапрос возвращает целую таблицу. Это самый распространенный вариант.

Очень часто возникают ситуации, когда мы хотим немного подправить определенную таблицу. И только потом объединить (посредством оператора 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 

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

Красным цветом отмечена исправленная ячейка.

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

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 (алиас) для вложенного запроса очень пригодился. У вложенного запроса в отличии от таблицы имени своего нет, поэтому псевдоним очень даже к месту.

И вот результат такого запроса:

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

Использование оператора 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