14 Ноя 2022
8 мин
96

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

Рассказываем, что такое подзапросы в SQL и как их использовать.

Что такое SQL подзапросы, где применяются и какие бывают

Содержание

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

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

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

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

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

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

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

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

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)

Синтаксис

Синтаксически подзапрос — это 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) — и таким образом рекомендовать их другим студентам.

Можно использовать следующий запрос:

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.

 


Получите пошаговый план развития в карьере после записи на курс






    Оставляя заявку, вы принимаете условия соглашения

    Учёба ради учёбы — это не прикольно. На наших курсах вы ставите конкретные цели и достигаете
    их
    в заданные сроки. Начинайте карьеру с первых достижений!

    Добавить комментарий

    Вставить формулу как
    Блок
    Строка
    Дополнительные настройки
    Цвет формулы
    Цвет текста
    #333333
    Используйте LaTeX для набора формулы
    Предпросмотр
    \({}\)
    Формула не набрана
    Вставить