SQL подзапросы: руководство по использованию

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

Что такое подзапросы в SQL

SQL-подзапрос — это SELECT-запрос, вложенный в другой запрос или подзапрос.

Подзапрос — это внутренний запрос. Внешний запрос — это оператор, который содержит подзапрос.

Подробнее об этом расскажет наш спикер в видео

[видео]

Для чего нужны

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

Такие задачи — не редкость в работе аналитика данных. Освоить эту профессию можно в Skypro на курсе «Аналитик данных». Там изучают основы SQL для получения и обработки информации.

Приведем пример на базе данных из трех таблиц: «Студенты», «Учебные курсы», «Оценки».

Объединим два последовательных запроса в один и найдем любимые студентами предметы — то есть те, по которым средний балл выше среднего балла всех предметов.

Для этого разобьем задачу на две части. Сначала найдем средний балл среди всех студентов по всем предметам:

Потом напишем запрос, который находит средний балл для каждого учебного предмета:

Чтобы найти любимые предметы, нужно в разделе HAVING подставить значение из первого запроса. В нашем случае — это HAVING avg_mark 3.4286. Если выполним такой запрос, получим корректный результат.

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

Так аналитики автоматизируют и ускоряют свою работу. Изучить основы SQL для решения задач анализа данных можно на курсе Skypro «Аналитик данных». А еще с помощью центра карьеры вы найдете работу по новой профессии уже в процессе учебы.

Ограничения

Хотя подзапросы — мощный инструмент в SQL, но и у них есть ограничения, которые важно учитывать для эффективной работы с базой данных.

1.  Производительность
   Подзапросы, особенно вложенные или коррелированные, могут медленнее выполнять запрос.
   Для каждой строки внешнего запроса нужно повторно выполнять подзапрос. Оптимизатор СУБД (систем управления базами данных) не всегда эффективно преобразует подзапросы в JOIN — в итоге планы выполняются неоптимально.
2. Ограничения в определенных операторах
   Некоторые операторы SQL накладывают ограничения на подзапросы:
   • В IN и NOT IN подзапрос должен возвращать один столбец.
   • В EXISTS подзапрос должен быть коррелированным (ссылаться на внешний запрос).
   • В SELECT (как часть выражения) подзапрос должен возвращать ровно одну строку и один столбец (скалярный подзапрос).

   Пример ошибки:
   SELECT name, (SELECT price FROM products) FROM customers;

3. Ограничения в UPDATE и DELETE
   В некоторых СУБД (например, MySQL) подзапросы в UPDATE и DELETE не могут ссылаться на изменяемую таблицу:
   DELETE FROM orders
WHERE customer_id IN (SELECT customer_id FROM orders WHERE total < 100);
   Используйте многотабличный синтаксис или временные таблицы, чтобы пройти через это ограничение.
4. Ограничения в GROUP BY и агрегатных функциях
   Подзапросы внутри GROUP BY или агрегатных функций могут быть запрещены или работать неочевидно:
   SELECT department, COUNT(*)
FROM employees
GROUP BY (SELECT department_id FROM departments WHERE location = 'NY');
5. Проблемы с NULL в NOT IN
   Если подзапрос в NOT IN возвращает NULL, весь оператор вернет пустой результат:
   SELECT * FROM products
WHERE id NOT IN (SELECT product_id FROM orders WHERE product_id IS NULL);
   Используйте NOT EXISTS или добавьте условие для исключения NULL:
   SELECT * FROM products
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.product_id = products.id);

Синтаксис

Синтаксически подзапрос — это SELECT-запрос, обернутый в круглые скобки ( , ). Подзапрос можно вложить в любой другой оператор. Или вкладывать подзапросы в подзапросы.

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

Типы вложенных запросов

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

Скалярное значение — это процесс, когда возвращается одно значение. Обычно это число или строка. Со скалярными значениями можно использовать операторы сравнения (<, >, =), передавать как аргумент функции или как значение колонки в операторе SELECT. Например, посчитаем для каждого ученика, какой процент курсов он посещал:

В табличном значении возвращается несколько строк. Заранее неизвестно, сколько — может, ноль, одна или больше. С табличными значениями используют операции IN, ANY, ALL, EXISTS, NOT EXISTS. Все эти операции проверяют вхождение строк(и) внешнего запроса в табличное значение, возвращаемое подзапросом. Еще табличное значение можно использовать в разделе FROM как таблицу-источник.

Пример подзапроса, который возвращает табличное значение. Найдем учебные курсы, где есть студенты хотя бы с одной отметкой. Это можно сделать с помощью оператора IN, который проверяет вхождение Classes.id в список ID классов с оценками:

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

Нужно аккуратно работать с NOT IN: если в список значений попадет NULL, результат выборки будет пустым:

Отличить подзапросы по возвращаемому значению очень просто: скалярные выбирают только одну колонку. А еще используют агрегатные функции без группировки GROUP BY. В таком случае СУБД видит, что запрос может вернуть только одну колонку и одну строку, то есть скалярное значение. Отсюда следует практическая рекомендация: если хочется использовать подзапрос как скалярное значение, нужно использовать агрегатную функцию.

Порядок выполнения подзапросов

По способу выполнения выделяют два типа подзапросов.

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

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

Сложные (коррелированные подзапросы — Correlated Subqueries). Такие подзапросы обращаются к полям внешнего запроса. СУБД будет вынуждена выполнить подзапрос для каждой строки и представить значение строки внешнего значения как параметр подзапроса.

Пример сложного подзапроса: найти всех студентов со средним баллом больше четырех. В этом примере важно отметить, что подзапрос использует Strudents.id из внешнего запроса.

Примеры вложенных запросов

Рассмотрим примеры вложенных запросов в разных операторах SQL.

SELECT

Выберем всех учеников, у которых по одному из курсов есть лучшая оценка среди всех студентов. Для этого воспользуемся операцией ALL. Синтаксически она немного отличается от уже рассмотренных операций: сначала идет оператор сравнения, потом ALL или ANY, после чего идет подзапрос в круглых скобках:

INSERT

В новую таблицу BestStudents2022 скопируем всех студентов со средней оценкой, которая больше, чем средняя оценка среди всех студентов. Воспользуемся конструкциями INSERT … SELECT. Они дают вставлять строки, которые возвращают SELECT-часть запроса.

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

UPDATE

Можно использовать подзапрос и изменить данные в таблицах. Например, отметить предметы, по которым есть более десяти оценок, как популярные (featured) — и так рекомендовать их другим студентам.

Аналитики делают так, чтобы данные в таблицах и на графиках обновлялись автоматически. А еще можно использовать программирование для визуализации данных. Всему этому учат на курсе Skypro «Аналитик данных».

Можно использовать такой запрос:

В результате два учебных класса отметим как популярные:

DELETE

SQL-подзапросы можно использовать с оператором DELETE. Давайте удалим все курсы, для которых нет ни одной оценки. Воспользуемся подзапросом и операцией NOT EXISTS:

Можно легко убедиться, что такие курсы удалили.

Краткие итоги

1. Подзапрос SQL — это запрос внутри другого запроса.
2. С помощью подзапросов можно использовать результат выполнения внутреннего запроса во внешнем запросе и конструировать сложные запросы из более простых.
3. Рассмотрели, как написать подзапрос, использовать операции IN, NOT IN, EXISTS, NOT EXISTS, ANY и ALL и подзапросы в SQL — операторах SELECT, INSERT, UPDATE и DELETE.