Запит всередині іншого: підзапити

Ви навчилися видобувати, фільтрувати, групувати, об’єднувати та перетворювати. Там остання фундаментальна ідея: помістити запит в інший. Це називаються підзапити, і вони є кроком, який дозволяє відповісти на запитання наприклад "хто заробляє вище середнього?" або "у яких відділах є хтось призначено для проекту?".

Основний синтаксис

Підзапит — це SELECT, взятий у круглі дужки, який використовується замість одного значення або список значень в іншому запиті:

-- Find who earns MORE THAN THE COMPANY AVERAGE:
SELECT first_name, last_name, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees);

Механізм спочатку виконує підзапит — SELECT AVG(salary) FROM employees повертає єдине число — і потім використовує це число у зовнішньому WHERE ніби ви це від руки написали. Саме в цьому й полягає перевага: ви робите не потрібно знати середнє значення заздалегідь, SQL обчислює його за вас.

Три найпоширеніші «типи»

1. Підзапит повертає одне значення

Приклад вище: підзапит створює один рядок, один стовпець (наз скаляр). Ви використовуєте його з =, >, < тощо.

-- Employee with the highest salary:
SELECT first_name, last_name
FROM   employees
WHERE  salary = (SELECT MAX(salary) FROM employees);

2. Підзапит повертає список

Підзапит створює один стовпець, багато рядків. Ви використовуєте його з IN (...):

-- Departments that have at least one employee:
SELECT name
FROM   departments
WHERE  id IN (SELECT DISTINCT department_id
              FROM employees
              WHERE department_id IS NOT NULL);

3. Підзапит як "таблиця" (FROM)

Підзапит також може зайняти місце таблиці в FROM. Це корисно для попередньо обчислити агрегати, а потім відфільтрувати або об’єднати їх:

-- Departments with average salary above 40000:
SELECT d.name, medie.stipendio_medio
FROM   departments AS d
JOIN  (SELECT department_id, AVG(salary) AS stipendio_medio
       FROM employees
       GROUP BY department_id) AS medie
   ON d.id = medie.department_id
WHERE  medie.stipendio_medio > 40000;

Підзапит чи JOIN? Коли використовувати який

Часто той самий результат можна отримати за допомогою JOIN або за допомогою підзапиту. Емпіричне правило:

• Щоб фільтрувати рядки однієї таблиці на основі властивості іншої ("співробітники, чиї відділи…"), IN (subquery) дуже читабельний.
• Щоб об’єднати стовпці з кількох таблиць у результат, використовуйте JOIN.
• Щоб обчислити агрегат і використовувати його як порогове значення ("вище середній"), скалярний підзапит є природним вибором.

На практиці ви побачите обидва: механізм SQL часто оптимізує їх однаково спосіб. Виберіть форму, яка найкраще читається.

Спробуйте

Повторна вправа