Пошук уроків, статей та іншого контенту
Навчіться використовувати вкладені запити в SELECT, WHERE та FROM для побудови складніших вибірок.
Підзапит — це SQL-запит, вкладений в інший запит. Результат внутрішнього запиту використовується зовнішнім запитом.
Підзапити дають змогу:
порівнювати значення з результатом іншого запиту;
перевіряти наявність пов’язаних записів;
обчислювати додаткові значення для кожного рядка;
використовувати результат одного запиту як тимчасову таблицю.
Підзапит зазвичай записують у круглих дужках:
SELECT *
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);Спочатку PostgreSQL обчислює середню ціну, а потім знаходить товари, дорожчі за це значення.
Для прикладів використаємо таблиці інтернет-магазину:
CREATE TEMP TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL,
city text NOT NULL
);
CREATE TEMP TABLE orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL,
amount numeric(10, 2) NOT NULL,
status text NOT NULL
);
INSERT INTO customers (id, name, city)
VALUES
(1, 'Олена', 'Київ'),
(2, 'Андрій', 'Львів'),
(3, 'Марія', 'Київ'),
(4, 'Петро', 'Одеса');
INSERT INTO orders (id, customer_id, amount, status)
VALUES
(1, 1, 1200.00, 'paid'),
(2, 1, 800.00, 'paid'),
(3, 2, 450.00, 'pending'),
(4, 3, 2100.00, 'paid'),
(5, 3, 300.00, 'cancelled');Таблиця customers містить клієнтів, а orders — їхні замовлення.
WHEREНайчастіше підзапит використовують у WHERE, щоб порівняти значення з результатом іншого запиту.
Знайдемо замовлення, сума яких більша за середню суму всіх замовлень:
SELECT id, customer_id, amount
FROM orders
WHERE amount > (
SELECT AVG(amount)
FROM orders
);Внутрішній запит:
SELECT AVG(amount)
FROM orders;повертає одне число. Зовнішній запит порівнює з ним значення amount кожного замовлення.
Підзапит у такому випадку називають скалярним, оскільки він повертає одне значення.
Скалярний підзапит повинен повертати:
один стовпець;
не більше одного рядка.
Якщо підзапит поверне кілька рядків, PostgreSQL повідомить про помилку:
more than one row returned by a subquery used as an expressionINОператор IN перевіряє, чи входить значення до набору значень, повернутих підзапитом.
Знайдемо клієнтів, які мають хоча б одне оплачене замовлення:
SELECT id, name
FROM customers
WHERE id IN (
SELECT customer_id
FROM orders
WHERE status = 'paid'
);Внутрішній запит повертає список ідентифікаторів:
1
3Зовнішній запит знаходить клієнтів, чиї id є в цьому списку.
Підзапит для IN може повернути багато рядків, але зазвичай має повертати один стовпець.
NOT INNOT IN знаходить значення, яких немає в результаті підзапиту.
Наприклад, знайдемо клієнтів без оплачених замовлень:
SELECT id, name
FROM customers
WHERE id NOT IN (
SELECT customer_id
FROM orders
WHERE status = 'paid'
);У цій конструкції потрібно бути уважними до NULL. Якщо підзапит повертає NULL, результат перевірки з NOT IN може бути неочікуваним. Для перевірки наявності пов’язаних рядків часто безпечніше використовувати NOT EXISTS.
EXISTSEXISTS перевіряє, чи повертає підзапит хоча б один рядок.
Знайдемо клієнтів, які мають оплачені замовлення:
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
AND o.status = 'paid'
);Значення 1 у SELECT 1 не має особливого значення. EXISTS перевіряє лише факт існування рядка, а не його значення.
Внутрішній запит пов’язаний із зовнішнім через умову:
o.customer_id = c.idТакий підзапит називають корельованим. Він використовує значення поточного рядка зовнішнього запиту.
Для кожного клієнта PostgreSQL перевіряє:
чи є замовлення цього клієнта;
чи має воно статус paid;
якщо хоча б одне знайдено — клієнт проходить умову EXISTS.
Протилежний оператор NOT EXISTS перевіряє, що підзапит не повертає жодного рядка:
SELECT c.id, c.name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
);Цей запит знаходить клієнтів, у яких немає жодного замовлення.
SELECTПідзапит у списку SELECT дає змогу обчислити додаткове значення для кожного рядка.
Наприклад, виведемо кожного клієнта та загальну суму його замовлень:
SELECT
c.id,
c.name,
(
SELECT COALESCE(SUM(o.amount), 0)
FROM orders AS o
WHERE o.customer_id = c.id
) AS total_amount
FROM customers AS c;Підзапит використовує c.id із зовнішнього запиту, тому він є корельованим.
Для кожного клієнта виконується логіка:
знайти його замовлення;
порахувати їхню суму;
якщо замовлень немає, повернути 0.
Функція COALESCE замінює NULL на вказане значення. Для клієнта без замовлень SUM повернула б NULL, тому використано:
COALESCE(SUM(o.amount), 0)Підзапит у SELECT повинен повертати одне значення для кожного рядка зовнішнього запиту. Тому такий варіант може спричинити помилку:
SELECT
c.name,
(
SELECT o.amount
FROM orders AS o
WHERE o.customer_id = c.id
) AS amount
FROM customers AS c;Якщо в клієнта кілька замовлень, внутрішній запит поверне кілька рядків. У такому разі потрібно використати агрегатну функцію, наприклад SUM, MAX або COUNT.
FROMПідзапит у FROM створює тимчасовий набір даних, який називають похідною таблицею.
Знайдемо загальну суму замовлень для кожного клієнта, а потім залишимо лише тих, хто витратив більше 1000:
SELECT
customer_id,
total_amount
FROM (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
) AS customer_totals
WHERE total_amount > 1000;Спочатку виконується підзапит:
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;Він формує тимчасовий набір із підсумками для кожного клієнта.
Потім зовнішній запит фільтрує цей набір:
WHERE total_amount > 1000Підзапит у FROM обов’язково повинен мати псевдонім. У прикладі це:
AS customer_totalsБез псевдоніма PostgreSQL поверне помилку.
Підзапит у FROM можна з’єднати з іншою таблицею:
SELECT
c.name,
customer_totals.total_amount
FROM customers AS c
JOIN (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
) AS customer_totals
ON customer_totals.customer_id = c.id;Тут підзапит обчислює суму оплачених замовлень, а JOIN додає ім’я клієнта.
JOINОдна й та сама задача часто розв’язується як підзапитом, так і за допомогою JOIN.
Наприклад, клієнти з оплаченими замовленнями через IN:
SELECT id, name
FROM customers
WHERE id IN (
SELECT customer_id
FROM orders
WHERE status = 'paid'
);Ту саму вибірку можна записати через JOIN:
SELECT DISTINCT c.id, c.name
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.id
WHERE o.status = 'paid';DISTINCT потрібен, якщо клієнт має кілька оплачених замовлень. Інакше такий клієнт з’явиться у результаті кілька разів.
Підзапити зручно використовувати, коли потрібно:
перевірити існування записів через EXISTS;
порівняти значення з агрегатом;
логічно розділити складну операцію на етапи.
JOIN часто зручний, коли потрібно вивести стовпці з кількох таблиць.
ALL та ANYПідзапит може повертати набір значень, із яким порівнюють одне значення.
Оператор > ALL означає, що значення має бути більшим за кожне значення з підзапиту:
SELECT id, amount
FROM orders
WHERE amount > ALL (
SELECT amount
FROM orders
WHERE status = 'cancelled'
);Цей запит знаходить замовлення, сума яких більша за суму кожного скасованого замовлення.
Оператор > ANY означає, що значення має бути більшим хоча б за одне значення з підзапиту:
SELECT id, amount
FROM orders
WHERE amount > ANY (
SELECT amount
FROM orders
WHERE status = 'cancelled'
);Для простих вибірок частіше достатньо IN, EXISTS або порівняння з агрегатною функцією.
Розглянемо запит:
SELECT
c.name,
(
SELECT COUNT(*)
FROM orders AS o
WHERE o.customer_id = c.id
) AS orders_count
FROM customers AS c
WHERE c.city = 'Київ';Його логіка така:
З таблиці customers вибираються клієнти з міста Київ.
Для кожного знайденого клієнта запускається підзапит.
Підзапит рахує кількість його замовлень.
Результат підзапиту виводиться у стовпці orders_count.
Результатом будуть імена київських клієнтів та кількість їхніх замовлень.
Неправильно:
SELECT *
FROM orders
WHERE amount = (
SELECT amount
FROM orders
WHERE status = 'paid'
);Внутрішній запит може повернути кілька сум, а оператор = очікує одне значення.
Можливі виправлення:
SELECT *
FROM orders
WHERE amount IN (
SELECT amount
FROM orders
WHERE status = 'paid'
);або:
SELECT *
FROM orders
WHERE amount > (
SELECT AVG(amount)
FROM orders
WHERE status = 'paid'
);Для IN підзапит повинен повертати один стовпець:
SELECT id, name
FROM customers
WHERE id IN (
SELECT customer_id, amount
FROM orders
);Такий запит неправильний, оскільки внутрішній запит повертає два стовпці.
Правильний варіант:
SELECT id, name
FROM customers
WHERE id IN (
SELECT customer_id
FROM orders
);FROMНеправильно:
SELECT *
FROM (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
);Потрібно додати псевдонім:
SELECT *
FROM (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
) AS totals;NULLNULL означає відсутність значення. З ним не можна коректно порівнювати за допомогою = або <>.
Для перевірки потрібно використовувати:
WHERE column_name IS NULLабо:
WHERE column_name IS NOT NULLОсобливо уважними потрібно бути з NOT IN, якщо підзапит може повернути NULL. У таких випадках часто доречніше використати NOT EXISTS.
Знайдемо імена клієнтів, які витратили на оплачені замовлення більше за середню суму витрат клієнтів.
Спочатку підзапит у FROM обчислює витрати кожного клієнта:
SELECT
c.name,
totals.total_amount
FROM customers AS c
JOIN (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
) AS totals
ON totals.customer_id = c.id
WHERE totals.total_amount > (
SELECT AVG(total_amount)
FROM (
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
) AS customer_totals
);У цьому запиті:
перший підзапит обчислює суму оплачених замовлень для кожного клієнта;
другий підзапит обчислює середню суму серед клієнтів;
зовнішній запит залишає клієнтів із результатом вище середнього.
Підзапит — це запит усередині іншого SQL-запиту.
У WHERE підзапити використовують для порівняння та фільтрації.
IN перевіряє входження значення до набору результатів.
EXISTS перевіряє, чи існує хоча б один відповідний рядок.
У SELECT підзапит обчислює одне додаткове значення для кожного рядка.
У FROM підзапит створює тимчасову похідну таблицю.
Скалярний підзапит повинен повертати не більше одного рядка та одного стовпця.
Підзапит у FROM повинен мати псевдонім.
Для перевірки відсутності пов’язаних записів часто використовують NOT EXISTS.
Якщо підзапит повертає кілька рядків, потрібно використовувати IN, EXISTS, ANY, ALL або агрегатну функцію залежно від задачі.