Пошук уроків, статей та іншого контенту
Порівнюйте значення з результатами підзапитів за допомогою операторів ANY, SOME та ALL.
ANY та ALLОператори ANY і ALL дають змогу порівняти одне значення з усіма значеннями, які повертає підзапит.
Загальний синтаксис:
значення оператор ANY (підзапит)
значення оператор ALL (підзапит)Підзапит має повертати лише один стовпець, але може повертати багато рядків.
Наприклад:
salary > ANY (
SELECT salary
FROM employees
WHERE department = 'Підтримка'
)Цей вираз перевіряє, чи є зарплата працівника більшою хоча б за одну зарплату працівників відділу підтримки.
ANYВираз із ANY повертає TRUE, якщо порівняння істинне хоча б для одного значення з результату підзапиту.
salary > ANY (
SELECT salary
FROM employees
WHERE department = 'Підтримка'
)Якщо підзапит повернув зарплати 1200, 1400 і 1600, то перевірка еквівалентна такій логіці:
salary > 1200 OR salary > 1400 OR salary > 1600Тому в цьому прикладі достатньо, щоб зарплата була більшою за найменше значення з підзапиту.
SELECT name, salary
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department = 'Підтримка'
);Запит поверне працівників, чия зарплата більша хоча б за одну зарплату працівника з відділу Підтримка.
ANY можна використовувати з різними операторами порівняння:
value = ANY (subquery)
value <> ANY (subquery)
value > ANY (subquery)
value >= ANY (subquery)
value < ANY (subquery)
value <= ANY (subquery)SOMESOME — це синонім ANY. Він має таку саму поведінку та використовується в такому самому синтаксисі:
salary > SOME (
SELECT salary
FROM employees
WHERE department = 'Підтримка'
)Цей запит рівнозначний запиту з ANY:
salary > ANY (
SELECT salary
FROM employees
WHERE department = 'Підтримка'
)На практиці частіше використовують ANY, але SOME може бути корисним для читабельності запитів, де потрібно підкреслити значення «хоча б одне».
ALLВираз із ALL повертає TRUE, якщо порівняння істинне для кожного значення, яке повернув підзапит.
salary > ALL (
SELECT salary
FROM employees
WHERE department = 'Підтримка'
)Якщо підзапит повернув зарплати 1200, 1400 і 1600, то перевірка еквівалентна такій логіці:
salary > 1200 AND salary > 1400 AND salary > 1600Отже, зарплата має бути більшою за найбільше значення з підзапиту.
SELECT name, salary
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department = 'Підтримка'
);Запит поверне працівників, чия зарплата більша за зарплату кожного працівника з відділу Підтримка.
Оператор ALL також можна використовувати з різними операторами порівняння:
value = ALL (subquery)
value <> ALL (subquery)
value > ALL (subquery)
value >= ALL (subquery)
value < ALL (subquery)
value <= ALL (subquery)ANY та ALLНехай підзапит повертає значення:
10, 20, 30Тоді:
value > ANY (subquery)означає:
value > 10 OR value > 20 OR value > 30А:
value > ALL (subquery)означає:
value > 10 AND value > 20 AND value > 30Приклади для значення 25:
25 > ANY (10, 20, 30) — TRUE, оскільки 25 > 10 і 25 > 20;
25 > ALL (10, 20, 30) — FALSE, оскільки 25 не більше за 30.
Наведений скрипт створює тимчасову таблицю працівників і демонструє різні варіанти використання ANY, SOME та ALL.
DROP TABLE IF EXISTS employees;
CREATE TEMP TABLE employees (
id integer GENERATED ALWAYS AS IDENTITY,
name text NOT NULL,
department text NOT NULL,
salary numeric(10, 2) NOT NULL
);
INSERT INTO employees (name, department, salary)
VALUES
('Олена', 'Розробка', 3200),
('Максим', 'Розробка', 2800),
('Ірина', 'Підтримка', 1800),
('Дмитро', 'Підтримка', 2100),
('Софія', 'Аналітика', 2500),
('Андрій', 'Аналітика', 1900);
-- Працівники із зарплатою, більшою хоча б за одну зарплату підтримки.
SELECT name, department, salary
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department = 'Підтримка'
)
ORDER BY salary DESC;
-- Працівники із зарплатою, більшою за зарплату кожного працівника підтримки.
SELECT name, department, salary
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department = 'Підтримка'
)
ORDER BY salary DESC;
-- Працівники з відділів, у яких є працівник із зарплатою не меншою за 3000.
SELECT name, department, salary
FROM employees
WHERE department = ANY (
SELECT department
FROM employees
WHERE salary >= 3000
)
ORDER BY department, salary DESC;
-- Те саме порівняння із синонімом ANY.
SELECT name, department, salary
FROM employees
WHERE salary >= SOME (
SELECT salary
FROM employees
WHERE department = 'Підтримка'
)
ORDER BY salary DESC;У першому запиті достатньо перевищити найменшу зарплату у відділі підтримки.
У другому запиті потрібно перевищити найбільшу зарплату у відділі підтримки.
У третьому запиті підзапит повертає назви відділів, де є працівник із зарплатою не меншою за 3000. Зовнішній запит повертає всіх працівників таких відділів.
ANY і INВираз:
department = ANY (
SELECT department
FROM departments
)зазвичай еквівалентний:
department IN (
SELECT department
FROM departments
)Наприклад:
SELECT name
FROM employees
WHERE department = ANY (
SELECT department
FROM employees
WHERE salary >= 3000
);Можна записати так:
SELECT name
FROM employees
WHERE department IN (
SELECT department
FROM employees
WHERE salary >= 3000
);ANY є більш загальним варіантом, оскільки його можна використовувати не лише з =, а й з іншими операторами:
salary > ANY (subquery)
salary <= ANY (subquery)ALL і NOT INВираз:
value <> ALL (subquery)відповідає логіці:
value NOT IN (subquery)Наприклад:
SELECT name, department
FROM employees
WHERE department <> ALL (
SELECT department
FROM employees
WHERE salary >= 3000
);Цей запит повертає працівників, чиї відділи не збігаються з жодним відділом, у якому є працівник із зарплатою не меншою за 3000.
Водночас потрібно пам’ятати про NULL. NOT IN і <> ALL можуть повернути не TRUE, а NULL, якщо результат підзапиту містить NULL. Це пов’язано з тризначною логікою SQL.
Якщо підзапит не повертає жодного рядка, ANY та ALL поводяться по-різному:
ANY повертає FALSE;
ALL повертає TRUE.
Приклад:
SELECT 10 > ANY (
SELECT salary
FROM employees
WHERE department = 'Відсутній'
);Підзапит не повертає рядків, тому весь вираз має значення FALSE.
SELECT 10 > ALL (
SELECT salary
FROM employees
WHERE department = 'Відсутній'
);У цьому випадку вираз має значення TRUE: немає жодного значення, для якого перевірка була б хибною.
Така поведінка відповідає логіці:
«істинно хоча б для одного» — якщо елементів немає, умова не виконується;
«істинно для всіх» — якщо елементів немає, немає й порушення умови.
NULLПорівняння з NULL не дає TRUE або FALSE, воно дає NULL (UNKNOWN).
Наприклад, якщо підзапит повертає 10 і NULL:
SELECT 20 > ALL (
SELECT value
FROM (
VALUES (10), (NULL)
) AS numbers(value)
);Порівняння 20 > 10 істинне, але 20 > NULL має значення NULL. Оскільки ALL вимагає істинності для кожного значення, результатом усього виразу буде NULL.
Для ANY достатньо знайти хоча б одне істинне порівняння:
SELECT 20 > ANY (
SELECT value
FROM (
VALUES (10), (NULL)
) AS numbers(value)
);Результатом буде TRUE, оскільки 20 > 10 істинне.
Якщо жодне порівняння не є істинним, але хоча б одне має результат NULL, загальний результат також може бути NULL, а не FALSE.
У реченні WHERE і FALSE, і NULL не проходять фільтрацію, тому відповідний рядок не буде повернуто.
ANY або ALLНекоректно:
SELECT name
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE department = 'Підтримка'
);Якщо підзапит поверне кілька рядків, PostgreSQL повідомить про помилку: підзапит використано як скалярний, але він повертає більше одного рядка.
Правильно:
SELECT name
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department = 'Підтримка'
);або:
SELECT name
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department = 'Підтримка'
);Вибір оператора залежить від потрібної логіки: «хоча б одне» або «кожне».
ANYУмова:
salary > ANY (subquery)не означає «зарплата більша за середню зарплату». Вона означає «зарплата більша хоча б за одне значення».
Якщо потрібно порівняти значення із середнім, підзапит має повертати одне агреговане значення:
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);ALL, коли потрібне ANYУмова:
salary >= ALL (subquery)перевіряє перевищення або рівність найбільшому значенню, а не будь-якому значенню.
Якщо потрібно знайти працівників, чия зарплата більша хоча б за одну зарплату з підзапиту, використовуйте ANY:
salary > ANY (subquery)NULLПеред використанням ANY або ALL варто перевірити, чи може підзапит повертати NULL.
За потреби NULL можна виключити:
SELECT name, salary
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department = 'Підтримка'
AND salary IS NOT NULL
);ANY повертає TRUE, якщо порівняння істинне хоча б для одного рядка підзапиту.
SOME — синонім ANY.
ALL повертає TRUE, якщо порівняння істинне для кожного рядка підзапиту.
Підзапит для ANY або ALL має повертати один стовпець.
value = ANY (subquery) зазвичай відповідає value IN (subquery).
value <> ALL (subquery) відповідає логіці value NOT IN (subquery).
Для порожнього результату підзапиту ANY повертає FALSE, а ALL — TRUE.
NULL може змінити результат порівняння на NULL, тому його потрібно враховувати окремо.