Примеры сложных вложенных запросов: объединение EXISTS, IN, HAVING

Поздравляем, мы подошли к моменту, где становится по-настоящему интересно! Сегодня мы посмотрим, как комбинировать разные виды подзапросов, чтобы решать сложные задачи. EXISTS, IN, HAVING — то самое трио, которое позволит вам почувствовать себя магами баз данных. Будем извлекать данные из одной таблицы, фильтровать их по данным из другой, группировать, а потом фильтровать группировки. А как бонус — разберём, какие приемы помогают сделать запросы эффективнее.

Давайте начнём с постановки одной общей задачи, которую постепенно будем решать на протяжении лекции.

Постановка задачи

Предположим, у нас есть база данных университета с тремя таблицами:

Таблица students

Таблица courses

Таблица enrollments

Нужно выбрать всех студентов, которые:

1. Записаны хотя бы на один курс EXISTS.
2. Не имеют оценки по хотя бы одному из записанных курсов IN.
3. Принадлежат к группам, в которых средний балл выше 80 HAVING.

Решение с использованием EXISTS и IN

Шаг 1: Проверка записанных студентов (EXISTS). Начнём с простейшего условия. Нам нужно понять, кто из студентов записан на хотя бы один курс. Для этого можно использовать EXISTS.

SELECT name
FROM students s
WHERE EXISTS (
  SELECT 1
  FROM enrollments e
  WHERE e.student_id = s.id
);

1. Внешний запрос выбирает имена из таблицы students.
2. В подзапросе мы проверяем, есть ли записи в таблице enrollments, соответствующие конкретному студенту из внешнего запроса (WHERE e.student_id = s.id).
3. SELECT 1 используется для указания, что нам просто важно наличие записей, а не их содержание.

Результат:

Теперь мы знаем, кто из студентов записан на курсы. Но мы хотим больше. Мы хотим фильтровать их по отсутствию оценок.

Шаг 2: Проверка отсутствия оценки (IN + NULL). Теперь добавим фильтрацию: нам нужны только те студенты, у которых хотя бы по одному курсу оценка отсутствует. Здесь нам помогут IN и знание работы с NULL.

SELECT name
FROM students s
WHERE id IN (
  SELECT e.student_id
  FROM enrollments e
  WHERE e.grade IS NULL
);

1. Во внешнем запросе мы выбираем имена студентов.
2. Подзапрос формирует список student_id из таблицы enrollments, где grade IS NULL.

Результат:

Итак, Otto — единственный студент, у которого есть курс без оценки. Как драматично! Но работу мы ещё не закончили: нам нужно учесть только группы, чей средний балл выше 80.

Решение с использованием HAVING

Шаг 3: Группировка и фильтрация с HAVING.

Вот теперь настал момент объединить всё. Нам нужно:

1. Посчитать средний балл для каждой группы.
2. Отфильтровать группы, у которых средний балл выше 80.
3. Вывести студентов из этих групп, учитывая предыдущие условия.

SELECT name
FROM students s
WHERE s.group_id IN (
  SELECT group_id
  FROM students
  JOIN enrollments ON students.id = enrollments.student_id
  WHERE grade IS NOT NULL
  GROUP BY group_id
  HAVING AVG(grade) > 80
)
AND id IN (
  SELECT e.student_id
  FROM enrollments e
  WHERE e.grade IS NULL
);

1. Внешний запрос выбирает имена студентов, которые соответствуют всем условиям.
2. Первый подзапрос в WHERE возвращает список group_id для групп со средним баллом выше 80.
   • Мы объединяем students с enrollments, чтобы получить оценки.
   • Фильтруем только те записи, где grade IS NOT NULL.
   • Группируем данные по group_id.
   • Используем HAVING для фильтрации групп.
3. Второй подзапрос в WHERE проверяет, что студент имеет хотя бы один курс без оценки.
4. Обе части условия объединены через AND.

Результат:

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

Сравнение подходов: EXISTS vs IN

EXISTS лучше всего работает, когда вы хотите быстро проверить наличие записей. Он эффективен, так как останавливает поиск сразу, как только находит первую запись. Это особенно важно для больших таблиц.

В то же время IN полезен, когда фокус на содержании данных. Например, если выводим список идентификаторов (id) для последующей фильтрации. Но стоит помнить, что IN может стать медленным для подзапросов, возвращающих много значений.

Когда использовать HAVING

Для агрегированных данных, где вам нужно фильтровать на основе результатов, HAVING — лучший выбор. Но если вы можете переместить условие в WHERE (например, фильтрацию по столбцу), это упростит запрос и ускорит выполнение.

Полный пример

Для закрепления разберём ещё один пример: выбрать группы, где хотя бы один студент имеет оценку ниже 75, но при этом не записан на курс "Философия".

Напомним, наши таблицы:

Таблица students

Таблица courses

Таблица enrollments

SELECT DISTINCT group_id
FROM students s
WHERE group_id IN (
  SELECT s.group_id
  FROM students s
  JOIN enrollments e ON s.id = e.student_id
  WHERE e.grade < 75
)
AND group_id NOT IN (
  SELECT s.group_id                                 -- вложенный запрос 1-го уровня
  FROM students s
  JOIN enrollments e ON s.id = e.student_id
  WHERE e.course_id = (
    SELECT id FROM courses WHERE name = 'Философия' -- вложенный запрос 2-го уровня :P
  )
);

1. Первый подзапрос выбирает группы, в которых есть студенты с оценкой ниже 75.
2. Второй подзапрос исключает группы, связанные с курсом "Философия".
3. Мы комбинируем условия через IN и NOT IN, чтобы получить итоговый результат.

Результат:

Насколько это полезно?

В реальной жизни такие подходы спасают, когда приходится анализировать сложные взаимосвязи данных. Например:

• В аналитике для выделения "особенных" групп клиентов (VIP, проблемные и т.д.).
• В разработке рекомендательных систем, где мы фильтруем пользователя по множеству критериев.
• На собеседованиях, когда вас попросят оптимизировать сложный SQL-запрос.

Тренируйтесь! Это ваш путь к мастерству.

2

Задача

SQL SELF, 14 уровень, 3 лекция

Недоступна

Поиск студентов с отсутствующей оценкой

Поиск студентов с отсутствующей оценкой

Открыть

2

Задача

SQL SELF, 14 уровень, 3 лекция

Недоступна

Группировка и фильтрация с помощью HAVING

Группировка и фильтрация с помощью HAVING

Открыть