Вложенные запросы в SQL: виды и примеры

В языке SQL вложенные запросы или подзапросы представляют собой конструкции, при которых один запрос помещается внутрь другого. Вложенный запрос выполняется сначала, а его результат используется в основном запросе. В зависимости от результата вложенный запрос делится на три категории: возвращающий одно значение, возвращающий несколько значений и соотнесенный (коррелирующий) подзапрос.

Вложенный запрос, возвращающий одно значение

В этом типе подзапрос гарантированно возвращает единственное значение, которое сравнивается в основном запросе. Такой подзапрос обычно располагается в условии WHERE с операторами сравнения (например, =, <, >). Если вложенный запрос возвратит более одного значения, возникнет ошибка, так как сравнение станет неоднозначным.

Механизм работы: выполняется сначала вложенный запрос, который возвращает одно значение (например, уникальный идентификатор), после чего это значение подставляется в основной запрос для фильтрации данных.

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

Рассмотрим пример из базы данных университета с таблицами spec (специальности) и facult (факультеты). В таблице facult хранится уникальный код факультета в поле idfacult, а в таблице spec поле idfacult связывает специальность с факультетом.

Задача: получить список специальностей факультета с названием "математический". Запрос:

SELECT spec.namespec FROM spec WHERE spec.idfacult = (SELECT facult.idfacult FROM facult WHERE facult.namefacult = "математический");

Сначала выполняется вложенный запрос, который возвращает одно уникальное значение idfacult — допустим, 1 для "математический". Это значение подставляется в основной запрос, который фильтрует специальности по idfacult = 1.

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

Вложенный запрос, возвращающий список значений

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

Механика: внутренний запрос возвращает список значений (например, множество идентификаторов), который передается оператору IN во внешнем запросе. Внешний запрос проверяет наличие значения в этом списке и фильтрует записи соответствующим образом.

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

Пример. Пусть нужно найти все заявления, поданные на факультет "математический", но поскольку на факультете несколько специальностей, нужно учитывать все их коды.

Вложенный запрос возвращает список кодов специальностей для факультета с idfacult = 1:

SELECT spec.idspec FROM spec WHERE spec.idfacult = 1;

Если внутренний запрос возвращает, например, значения 1 и 6, то внешний запрос выбирает все заявления, относящиеся к этим специальностям:

SELECT * FROM application WHERE application.idspec IN (1,6);

Таким образом, итоговый набор содержит все заявления, поданные на все специальности, привязанные к факультету.

Соотнесенные (коррелирующие) подзапросы

Соотнесенный запрос — это подзапрос, который содержит ссылку на поле из внешнего запроса. В таком случае подзапрос исполняется для каждой строки внешнего запроса отдельно.

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

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

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

Пример. Для каждого абитуриента из таблицы application нужно найти заявление с максимальным идентификатором (то есть последнее по времени подачи заявление). При этом необходимо получить не только id заявления, но и все связанные с ним атрибуты (форма обучения, специальность, приоритет).

Группировка по абитуриенту с использованием MAX() и GROUP BY вернет лишь идентификаторы заявлений, но не другие атрибуты. Соотнесенный подзапрос решает эту задачу.

SELECT * FROM application outquery WHERE outquery.idapplication = (SELECT MAX(idapplication) FROM application innerquery WHERE outquery.idabiturient = innerquery.idabiturient);

Здесь outquery и innerquery — псевдонимы внешнего и внутреннего запросов. Внешний запрос перебирает каждую запись, получает id абитуриента. Внутренний подзапрос вычисляет максимальный id заявления для текущего абитуриента. После сравнения выводится только запись заявки с максимальным id для каждого абитуриента.

Типичные ошибки и ограничения при использовании вложенных запросов

Основные ошибки при работе с вложенными запросами связаны с некорректным ожиданием количества возвращаемых значений и неправильным оператором сравнения.

Если подзапрос с оператором = возвращает более одного значения, возникает ошибка «subquery returns more than one row». Это происходит, если внутренний запрос не обеспечивает уникальность результата.

Решение: использовать оператор IN вместо =. Однако при больших объемах данных IN может тормозить. В таких случаях стоит рассмотреть JOIN.

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

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

Сравнение с альтернативными методами: JOIN и агрегатные функции

Вложенные запросы можно заменить соединениями JOIN во многих типовых задачах.

Преимущество JOIN заключается в большей производительности и ясности запросов при работе с большими таблицами. JOIN на уровне SQL оптимизатора обычно эффективнее, так как базируется на оптимизированных алгоритмах соединения.

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

Пример. Задача найти специальности факультета математического может быть выполнена через JOIN:

SELECT spec.namespec FROM spec JOIN facult ON spec.idfacult = facult.idfacult WHERE facult.namefacult = "математический";

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

Типовой совет — при скорости работы важен JOIN; для подготовки отчетов с простой логикой приемлемы вложенные запросы.

Таблица: особенности видов вложенных запросов

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

Матрица условий выбора вида вложенного запроса

Выбор вида вложенного запроса зависит от характера задачи и объема данных. Ниже приведена матрица рекомендаций:

• При необходимости вернуть строго одно значение для фильтрации — использовать вложенный запрос с оператором =. Необходимо убедиться в уникальности результата вложенного запроса.
• Если требуется фильтровать по набору значений, когда подзапрос возвращает несколько элементов — применять оператор IN с вложенным запросом. При больших объемах рассмотреть альтернативу с JOIN.
• Для выбора агрегированных значений с условием на конкретную строку (например, максимальный или минимальный параметр для каждой группы) подходит соотнесенный подзапрос, при этом следует контролировать нагрузку и индексировать поля связки.

Сочетание видов подзапросов и других SQL-конструкций — залог эффективных решений.