Перейти в телеграм, чтобы получить результаты теста
Есть задачи, которые нельзя решить с помощью одного обычного запроса. Пример такой задачи — выборка всех записей со значением больше среднего по всей таблице. Для одного запроса нельзя и выбрать значения, и посчитать агрегатную функцию по всей таблице. Чтобы решить такие задачи, используют подзапросы.
Рассказываем в статье, что такое подзапросы в SQL и для чего они нужны.
Что такое подзапросы в SQL
SQL-подзапрос — это SELECT-запрос, вложенный в другой запрос или подзапрос.
Подзапрос — это внутренний запрос. Внешний запрос — это оператор, который содержит подзапрос.
Для чего нужны
Подзапросами пользуются, когда нужно использовать результат выполнения одного запроса в следующем запросе.
Такие задачи — не редкость в работе аналитика данных. Освоить эту профессию можно в Skypro на курсе «Аналитик данных». Там изучают основы SQL для получения и обработки информации.
Приведем пример на базе данных из трех таблиц: «Студенты», «Учебные курсы», «Оценки».
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 );
Объединим два последовательных запроса в один, чтобы найти любимые студентами предметы — то есть предметы, по которым средний балл выше среднего балла всех предметов.
Для этого разобьем задачу на две части. Сначала найдем средний балл среди всех студентов по всем предметам:
SELECT AVG(mark) FROM Marks; +-----------+ | AVG(mark) | +-----------+ | 3.4286 | +-----------+ 1 row in set (0.00 sec)
Потом напишем запрос, который находит средний балл для каждого учебного предмета:
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. Если выполним такой запрос, получим корректный результат.
Чтобы не заниматься ручной подстановкой значений в запросы, воспользуемся подзапросом. Для этого нужно вместо значения подставить тело запроса, обернув его в скобки:
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)
Так аналитики автоматизируют и ускоряют свою работу. Выучить основы SQL для решения задач анализа данных можно на курсе Skypro «Аналитик данных». А найти работу по новой профессии — еще в процессе обучения. В этом поможет центр карьеры.
Синтаксис
Синтаксически подзапрос — это SELECT-запрос, обернутый в круглые скобки ( , ). Подзапрос может быть вложен в любой другой оператор. Можно вкладывать подзапросы в подзапросы.
Вложенные запросы можно использовать практически во всех частях внешнего запроса — везде, где разрешено использовать значения.
Типы вложенных запросов
Результат выполнения подзапроса подставляют во внешний запрос. Подзапросы могут возвращать как скалярные значения, так и табличные значения. От типа возвращаемого значения зависит, с какими операциями имеет смысл использовать подзапрос.
Скалярное значение — это когда возвращается одно значение. Обычно это число или строка. Со скалярными значениями можно использовать операторы сравнения (<, >, =), можно передавать как аргумент функции или как значение колонки в операторе SELECT. Например, посчитаем для каждого ученика, какой процент курсов он посещал:
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 классов, для которых есть оценки:
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.
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, результат выборки будет пустым:
mysql> SELECT * FROM Students WHERE id NOT IN (1, 2, 3, NULL); Empty set (0.00 sec)
Отличить подзапросы по возвращаемому значению очень просто: скалярные выбирают только одну колонку. А еще используют агрегатные функции без группировки GROUP BY. В таком случае СУБД видит, что запрос может вернуть только одну колонку и одну строку, то есть скалярное значение. Отсюда следует практическая рекомендация: если хочется использовать подзапрос как скалярное значение, нужно использовать агрегатную функцию.
Порядок выполнения подзапросов
По способу выполнения выделяют два типа подзапросов.
Простые. Такие подзапросы не зависят от внешнего запроса. СУБД выполнит такой подзапрос один раз перед выполнением внешнего запроса — и позже будет использовать значение столько раз, сколько понадобится. Пример простого подзапроса: найти всех студентов, которые не записались ни на один курс:
SELECT * FROM Students WHERE id NOT IN (SELECT DISTINCT student_id FROM Marks);
Сложные (коррелированные подзапросы — Correlated Subqueries). Такие подзапросы обращаются к полям внешнего запроса. СУБД будет вынуждена выполнить подзапрос для каждой строки, подставляя значение строки внешнего значения как параметр подзапроса.
Пример сложного подзапроса: найти всех студентов со средним баллом больше четырех. В этом примере важно заметить, что подзапрос использует Strudents.id из внешнего запроса.
SELECT * FROM Students WHERE (SELECT AVG(mark) FROM Marks WHERE Students.id = Marks.student_id) > 4;
Примеры вложенных запросов
Рассмотрим примеры вложенных запросов в различных операторах SQL.
SELECT
Выберем всех учеников, у которых по одному из курсов есть лучшая оценка среди всех студентов. Для этого воспользуемся операцией ALL. Синтаксически она немного отличается от уже рассмотренных операций: сначала идет оператор сравнения, потом ALL или ANY, после чего следует подзапрос в круглых скобках:
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-часть запроса.
Нам нужно посчитать сразу два средних арифметических значения: средний балл для каждого студента и для всех студентов. Напишем запрос с двумя подзапросами: коррелированным и обычным.
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) — и таким образом рекомендовать их другим студентам.
Так аналитики делают, чтобы данные в таблицах и на графиках обновлялись автоматически. А еще можно использовать программирование для визуализации данных. Всему этому учат на курсе Skypro «Аналитик данных».
Можно использовать следующий запрос:
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
В результате два учебных класса отмечены как популярные:
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:
DELETE FROM Classes WHERE NOT EXISTS (SELECT * FROM Marks WHERE Marks.class_id = Classes.id);
Можно легко убедиться, что такие курсы удалены.
SELECT * FROM Classes; +----+----------------+----------+ | id | name | featured | +----+----------------+----------+ | 1 | Rocket science | 1 | | 2 | Coolinary | 1 | +----+----------------+----------+ 2 rows in set (0.00 sec)
Краткие итоги
Подзапрос SQL — это запрос внутри другого запроса. С помощью подзапросов можно использовать результат выполнения внутреннего запроса во внешнем запросе — а это позволяет нам конструировать сложные запросы из более простых.
А еще мы рассмотрели, как написать подзапрос, использовать операции IN, NOT IN, EXISTS, NOT EXISTS, ANY и ALL и подзапросы в SQL — операторах SELECT, INSERT, UPDATE и DELETE.
Добавить комментарий