Пошук уроків, статей та іншого контенту
Застосовуйте EXISTS для перевірки наявності пов’язаних рядків без зайвого отримання даних.
EXISTSEXISTS перевіряє, чи повертає підзапит хоча б один рядок:
якщо підзапит повертає один або більше рядків — результат TRUE;
якщо підзапит не повертає жодного рядка — результат FALSE.
EXISTS не використовує значення стовпців із підзапиту. Для нього важлива лише наявність рядка.
SELECT EXISTS (
SELECT 1
FROM orders
WHERE customer_id = 10
);Результат — одне логічне значення:
exists
--------
tЗазвичай у підзапиті пишуть SELECT 1, щоб підкреслити: конкретні дані отримувати не потрібно.
SELECT EXISTS (
SELECT 1
FROM orders
WHERE customer_id = 10
);Фактично так само працювали б і такі варіанти:
SELECT EXISTS (
SELECT customer_id
FROM orders
WHERE customer_id = 10
);SELECT EXISTS (
SELECT *
FROM orders
WHERE customer_id = 10
);Але SELECT 1 чіткіше передає намір запиту.
Найчастіше EXISTS використовують у WHERE, коли підзапит посилається на поточний рядок зовнішнього запиту.
Такий підзапит називають корельованим:
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
);Для кожного клієнта PostgreSQL перевіряє:
чи існує замовлення;
чи належить воно поточному клієнту;
якщо так — клієнт потрапляє до результату.
Запит повертає лише клієнтів, які мають хоча б одне замовлення.
Зверніть увагу на умову:
o.customer_id = c.idc.id походить із зовнішнього запиту, тому результат підзапиту залежить від поточного клієнта.
Наведений приклад можна виконати в PostgreSQL повністю. Тимчасові таблиці існують лише протягом поточного з’єднання.
CREATE TEMP TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TEMP TABLE orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers(id),
total numeric(10, 2) NOT NULL
);
INSERT INTO customers (id, name)
VALUES
(1, 'Олена'),
(2, 'Тарас'),
(3, 'Марія');
INSERT INTO orders (id, customer_id, total)
VALUES
(101, 1, 250.00),
(102, 1, 120.00),
(103, 3, 80.00);
-- Клієнти, які мають хоча б одне замовлення
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
)
ORDER BY c.id;Результат:
id | name
----+--------
1 | Олена
3 | МаріяТарас не потрапляє до результату, оскільки для нього немає жодного рядка в orders.
EXISTS замість перевірки кількостіЯкщо потрібно лише перевірити наявність пов’язаних рядків, EXISTS краще передає мету запиту, ніж підрахунок:
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
);Порівняйте з варіантом через COUNT:
SELECT c.id, c.name
FROM customers AS c
WHERE (
SELECT COUNT(*)
FROM orders AS o
WHERE o.customer_id = c.id
) > 0;Другий варіант спочатку рахує всі відповідні рядки, хоча для умови достатньо знати, що існує хоча б один. EXISTS безпосередньо виражає цю перевірку.
План виконання залежить від конкретного запиту, статистики та індексів, але семантично EXISTS потребує лише відповіді «є рядок чи немає».
NOT EXISTSNOT EXISTS перевіряє протилежну умову:
TRUE, якщо підзапит не повертає жодного рядка;
FALSE, якщо підзапит повертає хоча б один рядок.
Наприклад, знайдемо клієнтів без замовлень:
SELECT c.id, c.name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
)
ORDER BY c.id;Результат:
id | name
----+------
2 | ТарасNOT EXISTS зручно використовувати для пошуку рядків, для яких відсутня пов’язана сутність:
користувачів без платежів;
товарів без відгуків;
проєктів без активних задач;
клієнтів без замовлень.
У підзапиті можна перевіряти не просто наявність зв’язку, а наявність зв’язку з певною властивістю.
Наприклад, знайдемо клієнтів, які мають замовлення на суму понад 200:
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.total > 200
)
ORDER BY c.id;Результат:
id | name
----+--------
1 | ОленаУ клієнта може бути багато замовлень, але в результат він потрапить лише один раз. EXISTS не множить рядки зовнішнього запиту.
EXISTS і JOINТу саму задачу іноді можна розв’язати через JOIN:
SELECT DISTINCT c.id, c.name
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.id
ORDER BY c.id;Однак без DISTINCT клієнт із кількома замовленнями з’явиться кілька разів:
id | name
----+--------
1 | Олена
1 | Олена
3 | МаріяEXISTS краще підходить, коли потрібно саме відфільтрувати рядки за фактом наявності зв’язку:
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
);Через EXISTS не потрібно:
вибирати стовпці з пов’язаної таблиці;
усувати дублікати за допомогою DISTINCT;
групувати результати.
JOIN доречний, коли дані з обох таблиць потрібно показати в результаті. EXISTS — коли пов’язана таблиця використовується лише як умова.
NOT 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
);Еквівалентний варіант через LEFT JOIN виглядає так:
SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
WHERE o.id IS NULL;Для простої перевірки відсутності зв’язку NOT EXISTS часто є зрозумілішим: умова прямо читається як «не існує замовлення для цього клієнта».
NOT EXISTS і NOT INДля перевірки відсутності зв’язку іноді використовують NOT IN:
SELECT c.id, c.name
FROM customers AS c
WHERE c.id NOT IN (
SELECT o.customer_id
FROM orders AS o
);Такий запис може дати несподіваний результат, якщо підзапит повертає NULL. Порівняння з NULL має значення UNKNOWN, а не TRUE або FALSE. Через це умова з NOT IN може не повернути жодного рядка.
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
);Якщо стовпець orders.customer_id гарантовано має обмеження NOT NULL, обидва підходи можуть поводитися однаково. Проте для корельованої перевірки відсутності зв’язку NOT EXISTS зазвичай є безпечнішим і наочнішим вибором.
Корельований підзапит часто шукає рядки за зовнішнім ключем:
WHERE o.customer_id = c.idДля великої таблиці orders корисним може бути індекс на customer_id:
CREATE INDEX orders_customer_id_idx
ON orders (customer_id);Він може допомогти PostgreSQL швидше знаходити відповідні замовлення для кожного клієнта.
Однак індекс не потрібно додавати автоматично до кожного стовпця. Рішення варто перевіряти за допомогою плану виконання:
EXPLAIN
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
);EXPLAIN показує, який план обрав PostgreSQL. На вибір плану впливають розмір таблиць, статистика, селективність умови та наявні індекси.
Некорельований підзапит перевіряє одну й ту саму умову для всіх рядків:
SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.total > 200
);Цей запит поверне всіх клієнтів, якщо в системі існує хоча б одне замовлення на суму понад 200. Він не перевіряє, кому належить замовлення.
Для перевірки замовлення конкретного клієнта потрібен зв’язок:
WHERE o.customer_id = c.id
AND o.total > 200EXISTSEXISTS повертає логічний результат, а не дані з підзапиту:
SELECT EXISTS (
SELECT o.total
FROM orders AS o
);Цей запит повертає true або false, але не значення total. Якщо потрібно показати дані пов’язаного замовлення, використовуйте JOIN або окремий підзапит, який повертає потрібне значення.
JOIN без урахування дублікатівЯкщо один зовнішній рядок має кілька пов’язаних рядків, JOIN поверне його кілька разів. Для фільтрації за самим фактом існування зв’язку EXISTS уникає цієї проблеми.
NOT EXISTSУмова всередині NOT EXISTS має описувати саме зв’язок із зовнішнім рядком:
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
);Якщо забути o.customer_id = c.id, перевірятиметься відсутність будь-яких замовлень у всій таблиці, а не відсутність замовлень у конкретного клієнта.
EXISTS повертає TRUE, якщо підзапит має хоча б один рядок.
NOT EXISTS повертає TRUE, якщо підзапит не має жодного рядка.
Значення, вибране в підзапиті, не має значення для EXISTS; зазвичай використовують SELECT 1.
Корельований підзапит посилається на стовпці зовнішнього запиту.
EXISTS зручно застосовувати для фільтрації без дублювання рядків.
NOT EXISTS підходить для пошуку рядків без пов’язаних записів.
Для перевірки відсутності зв’язку NOT EXISTS зазвичай надійніший за NOT IN, особливо якщо можливі NULL.
Для великих таблиць умови зв’язку, наприклад customer_id, можуть потребувати індексації.