Курс по SQL

Урок 32: Коррелированные подзапросы: Использование коррелированных подзапросов

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

Основы коррелированных подзапросов

Коррелированный подзапрос — это подзапрос, который выполняется для каждой строки внешнего запроса. Он ссылается на столбцы из внешнего запроса. Рассмотрим пример, где мы выбираем имена студентов, у которых оценка выше средней оценки по их курсу:

SELECT Имя
FROM Студенты s
WHERE Оценка > (SELECT AVG(Оценка)
 FROM Студенты
 WHERE КурсID = s.КурсID);

Результат выполнения

Имя
Иван

Синтаксис коррелированных подзапросов

Синтаксис коррелированных подзапросов следующий:

SELECT столбцы
FROM таблица внешнего запроса
WHERE столбец оператор (SELECT столбец
 FROM таблица подзапроса
 WHERE условие, зависящее от внешнего запроса);

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

Использование оператора EXISTS

Оператор EXISTS часто используется с коррелированными подзапросами для проверки существования строк. Рассмотрим пример, где мы выбираем имена студентов, которые учатся на курсах, преподаваемых определенным преподавателем:

SELECT Имя
FROM Студенты s
WHERE EXISTS (SELECT 1
 FROM Курсы c
 WHERE c.ID = s.КурсID
 AND c.ПреподавательID = (SELECT ID FROM Преподаватели WHERE Имя = 'Петров'));

Результат выполнения

Имя
Иван

Использование оператора IN

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

SELECT Название
FROM Курсы c
WHERE c.ID IN (SELECT s.КурсID
 FROM Студенты s
 WHERE s.Оценка > 80);

Результат выполнения

Название
Математика

Коррелированные подзапросы в SELECT

Коррелированные подзапросы могут быть использованы в SELECT для создания вычисляемых столбцов. Рассмотрим пример, где мы выбираем имена студентов и среднюю оценку по их курсу:

SELECT Имя,
 (SELECT AVG(Оценка)
 FROM Студенты
 WHERE КурсID = s.КурсID) AS СредняяОценка
FROM Студенты s;

Результат выполнения

Имя | СредняяОценка
Иван | 85
Мария | 70

Упражнения

SELECT Имя
FROM Студенты s
WHERE Оценка > (SELECT AVG(Оценка)
 FROM Студенты
 WHERE КурсID = s.КурсID);

SELECT Имя
FROM Студенты s
WHERE EXISTS (SELECT 1
 FROM Курсы c
 WHERE c.ID = s.КурсID
 AND c.ПреподавательID = (SELECT ID FROM Преподаватели WHERE Имя = 'Иванов'));

SELECT Название
FROM Курсы c
WHERE c.ID IN (SELECT s.КурсID
 FROM Студенты s
 WHERE s.Оценка > 75);

SELECT Имя,
 (SELECT AVG(Оценка)
 FROM Студенты
 WHERE КурсID = s.КурсID) AS СредняяОценка
FROM Студенты s;