SQL подзапросы: руководство по использованию
t.me/bigdataiРассказываем, что такое подзапросы в SQL и как их использовать.
Есть задачи, которые нельзя решить с помощью одного обычного запроса. Пример такой задачи — выборка всех записей со значением больше среднего по всей таблице. Для одного запроса нельзя и выбрать значения, и посчитать агрегатную функцию по всей таблице. Чтобы решить такие задачи, используют подзапросы.
Рассказываем в статье, что такое подзапросы в SQL и для чего они нужны.
Что такое подзапросы в SQL
SQL-подзапрос — это SELECT-запрос, вложенный в другой запрос или подзапрос.
Подзапрос — это внутренний запрос. Внешний запрос — это оператор, который содержит подзапрос.
Для чего нужны подзапросы?
Подзапросами пользуются, когда нужно использовать результат выполнения одного запроса в следующем запросе.
Приведем пример на базе данных из трех таблиц: «Студенты», «Учебные курсы», «Оценки».
123456789101112131415161718192021
CREATE TABLE Students (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(32) NOT NULL,
surname VARCHAR(32) NOT NULL
);
CREATE TABLE Classes (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64) NOT NULL,
featured TINYINT(1) DEFAULT 0 NOT NULL
);
CREATE TABLE Marks (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
class_id INT NOT NULL,
mark TINYINT UNSIGNED NOT NULL,
CONSTRAINT `fk_Marks_student_id__id` FOREIGN KEY (`student_id`) REFERENCES Students(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_Marks_class_id__id` FOREIGN KEY (`class_id`) REFERENCES Classes(`id`) ON DELETE CASCADE
);
Объединим два последовательных запроса в один, чтобы найти любимые студентами предметы — то есть предметы, по которым средний балл выше среднего балла всех предметов.
Для этого разобьем задачу на две части. Сначала найдем средний балл среди всех студентов по всем предметам:
12345678
SELECT AVG(mark) FROM Marks;
+-----------+
| AVG(mark) |
+-----------+
| 3.4286 |
+-----------+
1 row in set (0.00 sec)
Потом напишем запрос, который находит средний балл для каждого учебного предмета:
123
SELECT Classes.id, Classes.name, AVG(mark) AS avg_mark
FROM Classes INNER JOIN Marks ON Classes.id = Marks.class_id
GROUP BY Classes.id;
Чтобы найти любимые предметы, нужно в разделе HAVING подставить значение из первого запроса. В нашем случае — это HAVING avg_mark 3.4286. Если выполним такой запрос, получим корректный результат.
Чтобы не заниматься ручной подстановкой значений в запросы, воспользуемся подзапросом. Для этого нужно вместо значения подставить тело запроса, обернув его в скобки:
1234567891011
SELECT Classes.id, Classes.name, AVG(mark) AS avg_mark
FROM Classes INNER JOIN Marks ON Classes.id = Marks.class_id
GROUP BY Classes.id
HAVING avg_mark (SELECT AVG(mark) FROM Marks);
+----+----------------+----------+
| id | name | avg_mark |
+----+----------------+----------+
| 1 | Rocket science | 4.0000 |
+----+----------------+----------+
1 row in set (0.00 sec)
Синтаксис
Синтаксически подзапрос — это SELECT-запрос, обернутый в круглые скобки ( , ). Подзапрос может быть вложен в любой другой оператор. Можно вкладывать подзапросы в подзапросы.
Вложенные запросы можно использовать практически во всех частях внешнего запроса — везде, где разрешено использовать значения.
Типы вложенных запросов
Результат выполнения подзапроса подставляют во внешний запрос. Подзапросы могут возвращать как скалярные значения, так и табличные значения. От типа возвращаемого значения зависит, с какими операциями имеет смысл использовать подзапрос.
Скалярное значение — это когда возвращается одно значение. Обычно это число или строка. Со скалярными значениями можно использовать операторы сравнения (<, >, =), можно передавать как аргумент функции или как значение колонки в операторе SELECT. Например, посчитаем для каждого ученика, какой процент курсов он посещал:
1234567891011121314
SELECT
Students.name,
Students.surname,
count(distinct Marks.class_id) / (SELECT count(*) FROM Classes) AS pcnt
FROM Students INNER JOIN Marks ON Students.id = Marks.student_id
GROUP BY Students.id;
+---------+----------+--------+
| name | surname | pcnt |
+---------+----------+--------+
| Philip | Fry | 1.0000 |
| Turanga | Leela | 1.0000 |
| Bender | Rodrigez | 0.5000 |
+---------+----------+--------+
3 rows in set (0.01 sec)
Табличное значение — когда возвращается несколько строк. Заранее неизвестно сколько: может, ноль, одна или больше. С табличными значениями используют операции IN, ANY, ALL, EXISTS, NOT EXISTS. Все эти операции проверяют вхождение строк(и) внешнего запроса в табличное значение, возвращаемое подзапросом. Еще табличное значение можно использовать в разделе FROM как таблицу-источник.
Пример подзапроса, возвращающего табличное значение. Найдем учебные курсы, где есть студенты с хотя бы одной отметкой. Это можно сделать с помощью оператора IN, который проверяет вхождение Classes.id в список ID классов, для которых есть оценки:
123456789
SELECT * FROM Classes
WHERE id IN (SELECT class_id FROM Marks);
+----+----------------+----------+
| id | name | featured |
+----+----------------+----------+
| 1 | Rocket science | 1 |
| 2 | Coolinary | 1 |
+----+----------------+----------+
2 rows in set (0.00 sec)
И с помощью оператора EXISTS, который проверяет для каждого учебного курса наличие хотя бы одной оценки в таблице Marks.
123456789
SELECT * FROM Classes
WHERE EXISTS (SELECT class_id FROM Marks WHERE Classes.id = Marks.class_id);
+----+----------------+----------+
| id | name | featured |
+----+----------------+----------+
| 1 | Rocket science | 1 |
| 2 | Coolinary | 1 |
+----+----------------+----------+
2 rows in set (0.00 sec)
Нужно быть аккуратным при работе с NOT IN: Если в список значений попадет NULL, результат выборки будет пустым:
123
mysql> SELECT * FROM Students WHERE id NOT IN (1, 2, 3, NULL);
Empty set (0.00 sec)
Отличить подзапросы по возвращаемому значению очень просто: скалярные выбирают только одну колонку. А еще используют агрегатные функции без группировки GROUP BY. В таком случае СУБД видит, что запрос может вернуть только одну колонку и одну строку, то есть скалярное значение. Отсюда следует практическая рекомендация: если хочется использовать подзапрос как скалярное значение, нужно использовать агрегатную функцию.
Порядок выполнения подзапросов
По способу выполнения выделяют два типа подзапросов.
Простые. Такие подзапросы не зависят от внешнего запроса. СУБД выполнит такой подзапрос один раз перед выполнением внешнего запроса — и позже будет использовать значение столько раз, сколько понадобится. Пример простого подзапроса: найти всех студентов, которые не записались ни на один курс:
12
SELECT * FROM Students
WHERE id NOT IN (SELECT DISTINCT student_id FROM Marks);
Сложные (коррелированные подзапросы — Correlated Subqueries). Такие подзапросы обращаются к полям внешнего запроса. СУБД будет вынуждена выполнить подзапрос для каждой строки, подставляя значение строки внешнего значения как параметр подзапроса.
Пример сложного подзапроса: найти всех студентов со средним баллом больше четырех. В этом примере важно заметить, что подзапрос использует Strudents.id из внешнего запроса.
12
SELECT * FROM Students
WHERE (SELECT AVG(mark) FROM Marks WHERE Students.id = Marks.student_id) > 4;
Примеры вложенных запросов
Рассмотрим примеры вложенных запросов в различных операторах SQL.
SELECT
Выберем всех учеников, у которых по одному из курсов есть лучшая оценка среди всех студентов. Для этого воспользуемся операцией ALL. Синтаксически она немного отличается от уже рассмотренных операций: сначала идет оператор сравнения, потом ALL или ANY, после чего следует подзапрос в круглых скобках:
1234567891011121314
SELECT S.name, S.surname, C.name
FROM Students AS S
INNER JOIN Marks AS M ON S.id = M.student_id
INNER JOIN Classes AS C ON C.id = M.class_id
WHERE M.mark >= ALL (SELECT mark FROM Marks WHERE M.class_id = Marks.class_id);
+---------+----------+----------------+
| name | surname | name |
+---------+----------+----------------+
| Philip | Fry | Rocket science |
| Bender | Rodrigez | Coolinary |
| Turanga | Leela | Rocket science |
+---------+----------+----------------+
3 rows in set (0.00 sec)
INSERT
В новую таблицу BestStudents2022 скопируем всех студентов со средней оценкой, которая больше, чем средняя оценка среди всех студентов. Воспользуемся конструкциями INSERT … SELECT. Они позволят вставлять строки, которые возвращают SELECT-часть запроса.
Нам нужно посчитать сразу два средних арифметических значения: средний балл для каждого студента и для всех студентов. Напишем запрос с двумя подзапросами: коррелированным и обычным.
12345678910111213
INSERT INTO BestStudents2022(`name`, `surname`)
SELECT name, surname FROM Students
WHERE (SELECT AVG(mark) FROM Marks WHERE Students.id = Marks.student_id) > (SELECT AVG(mark) FROM Marks);
SELECT name, surname FROM BestStudents2022;
+---------+----------+
| name | surname |
+---------+----------+
| Philip | Fry |
| Turanga | Leela |
| Bender | Rodrigez |
+---------+----------+
3 rows in set (0.00 sec)
UPDATE
Можно использовать подзапрос, чтобы изменить данные в таблицах. Например, можно отметить предметы, по которым есть более десяти оценок, как популярные (featured) — и таким образом рекомендовать их другим студентам.
Можно использовать следующий запрос:
123456
UPDATE Classes
SET featured = 1
WHERE (SELECT count(*) FROM marks WHERE class_id = id) > 10;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
В результате два учебных класса отмечены как популярные:
123456789
mysql> SELECT * FROM Classes;
+----+----------------+----------+
| id | name | featured |
+----+----------------+----------+
| 1 | Rocket science | 1 |
| 2 | Coolinary | 1 |
| 3 | Hospitality | 0 |
+----+----------------+----------+
3 rows in set (0.00 sec)
DELETE
SQL-подзапросы можно использовать с оператором DELETE. Давайте удалим все курсы, для которых нет ни одной оценки. Воспользуемся подзапросом и операцией NOT EXISTS:
12
DELETE FROM Classes
WHERE NOT EXISTS (SELECT * FROM Marks WHERE Marks.class_id = Classes.id);
Можно легко убедиться, что такие курсы удалены.
12345678
SELECT * FROM Classes;
+----+----------------+----------+
| id | name | featured |
+----+----------------+----------+
| 1 | Rocket science | 1 |
| 2 | Coolinary | 1 |
+----+----------------+----------+
2 rows in set (0.00 sec)
Вложенный запрос, операторы ANY и ALL
Вложенный запрос, возвращающий несколько значений одного столбца, можно использовать для отбора записей с помощью операторов ANY и ALL совместно с операциями отношения (=, <>, <=, >=, <, >).
Операторы ANY и ALL используются в SQL для сравнения некоторого значения с результирующим набором вложенного запроса, состоящим из одного столбца. При этом тип данных столбца, возвращаемого вложенным запросом, должен совпадать с типом данных столбца (или выражения), с которым происходит сравнение.
При использовании оператора ANY в результирующую таблицу будут включены все записи, для которых выражение со знаком отношения верно хотя бы для одного элемента результирующего запроса. Как работает оператор ANY:
amount > ANY (10, 12)эквивалентноamount > 10amount < ANY (10, 12)эквивалентноamount < 12amount = ANY (10, 12)эквивалентно(amount = 10) OR (amount = 12), а такжеamount IN (10,12)amount <> ANY (10, 12)вернет все записи с любым значениемamount,включая 10 и 12
При использовании оператора ALL в результирующую таблицу будут включены все записи, для которых выражение со знаком отношения верно для всех элементов результирующего запроса. Как работает оператор ALL:
amount > ALL (10, 12)эквивалентноamount > 12amount < ALL (10, 12)эквивалентноamount < 10amount = ALL (10, 12)не вернет ни одной записи, так как эквивалентно(amount = 10) AND (amount = 12)amount <> ALL (10, 12)вернет все записи кроме тех, в которыхamount равно 10 или 12
Важно! Операторы ALL и ANY можно использовать только с вложенными запросами. В примерах выше (10, 12) приводится как результат вложенного запроса просто для того, чтобы показать как эти операторы работают. В запросах так записывать нельзя.
Пример
Вывести информацию о тех книгах, количество которых меньше самого маленького среднего количества книг каждого автора.
Запрос:
SELECT title, author, amount, price
FROM book
WHERE amount < ALL (
SELECT AVG(amount)
FROM book
GROUP BY author
);
Результат:
+--------------------+------------------+--------+--------+ | title | author | amount | price | +--------------------+------------------+--------+--------+ | Мастер и Маргарита | Булгаков М.А. | 3 | 670.99 | | Братья Карамазовы | Достоевский Ф.М. | 3 | 799.01 | +--------------------+------------------+--------+--------+
Пояснение
1. Вложенный запрос
SELECT AVG(amount) FROM book GROUP BY author
отбирает следующие записи:
+-------------+ | AVG(amount) | +-------------+ | 4.0000 | | 7.6667 | | 15.0000 | +-------------+
2. Условие отбора в основном запросе
amount < ALL ( SELECT AVG(amount) FROM book GROUP BY author )
можно переписать (если заменить вложенный запрос списком отобранных значений):
amount < ALL ( 4.0000, 7.6667, 15.0000)
что в соответствии с определением ALL, это значит, что подходят все amount меньшие 4.000.
Таким образом, наш запрос отобрал все книги книги Мастер и Маргарита и Братья Карамазовы, количество которых равно 3.
Пример
Вывести информацию о тех книгах, количество которых меньше самого большого среднего количества книг каждого автора.
Запрос:
SELECT title, author, amount, price
FROM book
WHERE amount < ANY (
SELECT AVG(amount)
FROM book
GROUP BY author
);
Результат:
+--------------------+------------------+--------+--------+ | title | author | amount | price | +--------------------+------------------+--------+--------+ | Мастер и Маргарита | Булгаков М.А. | 3 | 670.99 | | Белая гвардия | Булгаков М.А. | 5 | 540.50 | | Идиот | Достоевский Ф.М. | 10 | 460.00 | | Братья Карамазовы | Достоевский Ф.М. | 3 | 799.01 | | Игрок | Достоевский Ф.М. | 10 | 480.50 | +--------------------+------------------+--------+--------+
Пояснение
В этом примере amount < ANY ( 4.0000, 7.6667, 15.0000) означает, что подходят amount меньше самого большого значения из списка.
Задание
Вывести информацию о книгах(автор, название, цена), цена которых меньше самой большой из минимальных цен, вычисленных для каждого автора.
Результат
+------------------+---------------+--------+ | author | title | price | +------------------+---------------+--------+ | Булгаков М.А. | Белая гвардия | 540.50 | | Достоевский Ф.М. | Идиот | 460.00 | | Достоевский Ф.М. | Игрок | 480.50 | +------------------+---------------+--------+
Структура и наполнение таблицы book
+---------+-----------------------+------------------+--------+--------+ | book_id | title | author | price | amount | +---------+-----------------------+------------------+--------+--------+ | 1 | Мастер и Маргарита | Булгаков М.А. | 670.99 | 3 | | 2 | Белая гвардия | Булгаков М.А. | 540.50 | 5 | | 3 | Идиот | Достоевский Ф.М. | 460.00 | 10 | | 4 | Братья Карамазовы | Достоевский Ф.М. | 799.01 | 3 | | 5 | Игрок | Достоевский Ф.М. | 480.50 | 10 | | 6 | Стихотворения и поэмы | Есенин С.А. | 650.00 | 15 | +---------+-----------------------+------------------+--------+--------+