Теория: Подзапросы

В этом уроке мы познакомимся с подзапросами. Их используют, когда в один запрос надо передать результат другого запроса.

Некоторые задачи можно решить как с помощью подзапросов, так и с помощью соединений. Мы сравним эти два способа позже, а в этом уроке научимся использовать подзапросы и узнаем, какие они бывают.

Применение подзапросов

Допустим, у нас есть таблицы с сотрудниками и отделами, в которых они работают:

departments

employees

View on DB Fiddle

Наша задача — по названию отдела получить список работающих в нем сотрудников. Эту задачу можно решить с помощью соединения и фильтрации, но сейчас мы пойдем другим путем. Сначала мы решим задачу с помощью двух запросов, а потом объединим их в решение с подзапросом.

Разобьем решение на два этапа:

Первый этап. Найдем идентификатор отдела по его названию:

Первый запрос

SELECT dep.department_id
FROM departments AS dep
WHERE dep.name = 'Development Department'

View on DB Fiddle

Названия отделов уникальны, поэтому запрос вернет одну строку с идентификатором отдела:

Второй этап. Воспользуемся найденным идентификатором и построим список сотрудников отдела:

Второй запрос

SELECT emp.name AS employee
FROM employees AS emp
WHERE emp.department_id = 1

Результат выполнения запроса:

View on DB Fiddle

Мы решили задачу, но у такого способа есть недостаток — необходимо запомнить результат первого запроса и потом перенести его во второй запрос. Это либо дополнительная ручная работа, либо дополнительный код, который нужно добавить в приложение, работающее с базой данных.

Теперь рассмотрим решение этой же задачи с помощью подзапроса:

SELECT emp.name AS employee
FROM employees AS emp
WHERE emp.department_id = (
    SELECT dep.department_id
    FROM departments AS dep
    WHERE dep.name = 'Development Department'
)

View on DB Fiddle

Вместо того чтобы переносить результат первого запроса во второй, мы встроили первый запрос внутрь второго. Теперь СУБД сама определит значение и подставит его.

Запрос, который находится внутри другого запроса, называется подзапросом или вложенным запросом. Подзапросы можно использовать почти в любом месте основного запроса, где можно использовать обычные значения. Нужно только окружить подзапрос круглыми скобками.

Мы используем подзапрос вместо значения, значит, это обязательно должен быть SELECT-запрос с одним столбцом. При этом внешний запрос может быть любого типа: SELECT, INSERT, UPDATE, DELETE.

Далее мы изучим, какие бывают разновидности подзапросов.

Подзапросы, которые возвращают набор значений

Подзапросом можно заменить не только простое значение, но и набор значений. Рассмотрим такой пример.

Предположим, что нам нужно построить список всех сотрудников, работающих в определенном городе. Решение с помощью подзапросов выглядит так:

SELECT emp.name AS employee
FROM employees AS emp
WHERE emp.department_id IN (
    SELECT dep.department_id
    FROM departments AS dep
    WHERE dep.city = 'Vladivostok'
);

Подзапрос здесь возвращает список идентификаторов отделов, расположенных в определенном городе:

View on DB Fiddle

Этот результат будет подставлен в основной запрос, то есть условие фильтрации станет таким: emp.department_id IN (2, 5). Оно проверяет, что emp.department_id равен одному из значений в списке (2, 5).

Даже если подзапрос возвращает одну или ноль строк, с ним все равно можно работать как с набором значений. Например, пользоваться операторами IN, NOT IN, EXISTS и NOT EXISTS.

А вот обратное — не верно. Допустим, мы работаем с подзапросом как с простым значением, например, используем операторы сравнения =, >, < и так далее. Если такой запрос вернет больше одной строки, то СУБД не сможет выполнить основной запрос и вернет ошибку:

Поэтому программист должен быть очень аккуратен, когда использует подзапросы как простые значения.

Мы можем разделить подзапросы на возвращающие простые значения, и на те, которые возвращают набор значений. Подзапрос должен возвращать ровно один столбец. Давайте попробуем в подзапросе использовать символ *:

SELECT dep.department_id
FROM departments AS dep
WHERE dep.city = 'Vladivostok';

SELECT emp.name AS employee
FROM employees AS emp
WHERE emp.department_id IN (
    SELECT *
    FROM departments AS dep
    WHERE dep.city = 'Vladivostok'
);

https://www.db-fiddle.com/f/qX1gcqrUQCAtP1DwiRRiXU/6

Этот запрос вернет ошибку, которая оповещает, что в подзапросе содержится много столбцов.

Коррелированные подзапросы

В предыдущих примерах подзапросы не зависели от основного запроса. Поэтому СУБД может один раз выполнить подзапрос, запомнить результат и затем выполнить основной запрос.

Но бывает и другой тип подзапросов — коррелированные подзапросы. Они обращаются к полям основного запроса. Рассмотрим такой пример.

Наша задача — для всех отделов получить название и количество сотрудников. Эту задачу можно решить с помощью соединений, но в этом уроке мы воспользуемся подзапросом:

SELECT
    main_query_department.name AS department_title,
    (
        SELECT COUNT(*)
        FROM employees AS subquery_employee
        WHERE
            subquery_employee.department_id
            = main_query_department.department_id
    ) AS employees_count
FROM departments AS main_query_department

Результат выполнения запроса:

View on DB Fiddle

Подзапрос считает количество сотрудников в отделе, но вот идентификатор отдела заранее не известен — мы берем его из основного запроса: subquery_employee.department_id = main_query_department.department_id. Поэтому для каждого отдела подзапрос будет выполнен заново.

Коррелированный подзапрос может возвращать как простое значение, так и набор значений.

Коррелированный подзапрос — удобный инструмент, но пользоваться им надо очень аккуратно, потому что он выполняется для каждой строки основного запроса. Если подзапрос работает всего одну сотую секунды, а основной запрос перебирает один миллион строк, то общее время будет больше двух с половиной часов.

Выводы

Подзапросы — это запросы внутри запросов. Они позволяют использовать результат одного запроса внутри другого запроса.

Подзапрос можно использовать как простое значение или как набор значений. В первом случае обязательно, чтобы подзапрос возвращал не больше одной строки и только один столбец

Если подзапрос обращается к полям основного запроса, то его называют коррелированным. Такие подзапросы запускаются заново для каждой строки основного запроса, и общее время выполнения может быть очень большим.