Приклади складних вкладених запитів: об'єднання 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-запит.

Тренуйся! Це твій шлях до майстерності.