ALL в Oracle SQL — как сравнивать с множеством значений

 ALL в Oracle SQL. Введение

Оператор ALL в Oracle SQL используется для сравнения значения с результатом подзапроса, возвращающего множество значений. Это мощный инструмент, особенно при построении условий, где требуется проверка соответствия с всеми элементами множества. В отличие от ANY или SOME, ALL требует, чтобы условие было выполнено для всех возвращённых значений.

🖋️ Синтаксис оператора ALL

выражение оператор ALL (подзапрос)

• выражение — значение, которое сравнивается
• оператор — =, !=, >, <, >=, <=
• подзапрос — SELECT, возвращающий один столбец значений

Например:

SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 10);

🔄 Где используется ALL в Oracle SQL

• Проверка минимальности или максимальности по сравнению с группой
• Построение фильтров «больше всех», «меньше всех»
• Использование в подзапросах внутри WHERE, HAVING
• Анализ значений в связанной таблице

📊 40 примеров использования ALL в Oracle SQL

1. Найти сотрудников, чья зарплата выше, чем у всех в отделе 50:

SELECT * FROM employees
WHERE salary > ALL (SELECT salary 
                      FROM employees WHERE department_id = 50);

2. Сравнение меньше всех значений:

SELECT * FROM employees
WHERE hire_date < ALL (SELECT hire_date 
                         FROM employees WHERE job_id = 'IT_PROG');

3. Использование NOT = ALL:

SELECT * FROM employees
WHERE department_id != ALL (SELECT department_id 
                              FROM departments WHERE location_id = 1700);

4. Фильтр по дате позже всех других:

SELECT * FROM orders
WHERE order_date > ALL (SELECT order_date 
                          FROM orders WHERE customer_id = 101);

5. Сравнение с числовым подзапросом:

SELECT * FROM products
WHERE price < ALL (SELECT price 
                     FROM products WHERE category = 'Electronics');

6. Использование ALL с подзапросом по регионам:

SELECT * FROM stores
WHERE region_id > ALL (SELECT region_id 
                         FROM regions WHERE country_id = 'US');

7. Подзапрос в SELECT с ALL:

SELECT employee_id, first_name FROM employees
WHERE commission_pct >= ALL (SELECT 0.1 FROM dual);

8. ALL с агрегатной функцией:

SELECT * FROM employees
 WHERE salary >= ALL (SELECT MAX(salary) 
  FROM employees WHERE department_id IN (10, 20));

9. Проверка по ALL и вложенным подзапросам:

SELECT * FROM employees
WHERE department_id > ALL (
  SELECT department_id FROM departments WHERE location_id = (
    SELECT location_id FROM locations WHERE city = 'London')
);

10. ALL с датами в формате TO_DATE:

SELECT * FROM meetings
WHERE meeting_date < ALL (
  SELECT TO_DATE('2024-12-01', 'YYYY-MM-DD') FROM dual
);

11. Сравнение зарплаты с подзапросом из двух отделов:

SELECT * FROM employees
WHERE salary > ALL (SELECT salary 
                      FROM employees WHERE department_id IN (60, 90));

12. Сравнение значений из внешней таблицы:

SELECT * FROM inventory
WHERE quantity < ALL (SELECT quantity FROM external_stock);

13. ALL с NOT IN эквивалентом:

SELECT * FROM employees
WHERE job_id != ALL (SELECT job_id 
                       FROM jobs WHERE job_title LIKE '%Manager%');

14. Сравнение возраста сотрудников с группой:

SELECT * FROM people
WHERE age > ALL (SELECT age FROM trainees);

15. ALL в аналитическом контексте:

SELECT * FROM employees e
WHERE salary >= ALL (
  SELECT AVG(salary) FROM employees WHERE department_id = e.department_id
);

16. Использование в HAVING:

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > ALL (
  SELECT AVG(salary) FROM employees WHERE commission_pct IS NOT NULL
);

17. Сравнение дат регистрации:

SELECT * FROM users
WHERE registration_date < ALL (SELECT registration_date FROM premium_users);

18. Сравнение ID с другими таблицами:

SELECT * FROM invoices
WHERE invoice_id > ALL (SELECT payment_id FROM payments);

19. Сравнение цен на продукты:

SELECT * FROM products
WHERE price <= ALL (SELECT price FROM products WHERE category = 'Books');

20. ALL в подзапросе в SELECT:

SELECT first_name, (salary > ALL (SELECT salary 
                                    FROM employees 
                                    WHERE department_id = 90)) AS top_paid
FROM employees;

21. ALL с подзапросом из UNION:

SELECT * FROM employees
WHERE salary > ALL (
  SELECT salary FROM hr_employees
  UNION
  SELECT salary FROM sales_employees
);

22. Сравнение всех дат с минимальной:

SELECT * FROM logs
WHERE log_date > ALL (
  SELECT start_time FROM jobs
);

23. Использование ALL в WITH CTE:

WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_sal 
   FROM employees GROUP BY department_id
)
SELECT * FROM employees
WHERE salary > ALL (SELECT avg_sal FROM dept_avg);

24. Сравнение оценки кандидатов:

SELECT * FROM candidates
WHERE score >= ALL (SELECT score 
                      FROM applicants WHERE job_id = 'J_DEV');

25. Проверка максимальности статуса:

SELECT * FROM orders
WHERE status_id > ALL (SELECT status_id 
                         FROM order_status WHERE is_final = 'Y');

26. Подзапрос с фильтрацией NULL:

SELECT * FROM employees
WHERE salary > ALL (
  SELECT salary FROM employees WHERE department_id = 100 AND salary IS NOT NULL
);

27. ALL с функцией TO_NUMBER:

SELECT * FROM payments
WHERE amount > ALL (SELECT TO_NUMBER(xml_amount) FROM xml_payments);

28. ALL с параметром:

SELECT * FROM employees
WHERE salary > ALL (
  SELECT salary FROM employees WHERE department_id = :p_dept
);

29. Проверка меньше всех значений по другим офисам:

SELECT * FROM offices o1
WHERE o1.rent < ALL (SELECT o2.rent FROM offices o2 WHERE o2.city != o1.city);

30. ALL с датами из JSON_TABLE:

SELECT * FROM events
WHERE event_date < ALL (
  SELECT TO_DATE(j.event_dt, 'YYYY-MM-DD')
  FROM JSON_TABLE(:json, '$.events[*]' COLUMNS (event_dt VARCHAR2(10) PATH '$.date')) j
);

31. Сравнение заказов по количеству:

SELECT * FROM orders
WHERE item_count > ALL (SELECT item_count FROM orders WHERE customer_id = 55);

32. ALL с FILTER и агрегатами:

SELECT * FROM employees
WHERE salary > ALL (
  SELECT MAX(salary) KEEP (DENSE_RANK FIRST ORDER BY hire_date)
  FROM employees WHERE department_id = 60
);

33. Использование с условием BETWEEN:

SELECT * FROM inventory
WHERE quantity NOT BETWEEN 0 AND ALL (SELECT max_qty FROM limits);

34. Сравнение с аналитической функцией:

SELECT * FROM employees
WHERE salary > ALL (
  SELECT RANK() OVER (ORDER BY salary DESC) FROM employees
);

35. Использование ALL и LIKE:

SELECT * FROM users
WHERE username != ALL (
  SELECT username FROM admins WHERE username LIKE 'SYS%'
);

36. ALL внутри EXISTS:

SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE c.customer_id = o.customer_id
  AND o.amount > ALL (SELECT amount FROM refunds)
);

37. Сравнение числа с ALL датой (невозможно — ошибка):

-- Пример ошибки использования:
SELECT * FROM employees
WHERE salary > ALL (SELECT hire_date FROM employees); -- Типовая ошибка типов

38. Подзапрос ALL в скалярной подстановке:

SELECT e.*, (e.salary > ALL (SELECT salary 
                               FROM employees WHERE department_id = 80)) AS top_flag
FROM employees e;

39. Сравнение ALL со вложенной логикой:

SELECT * FROM products
WHERE stock > ALL (
  SELECT AVG(stock) FROM products WHERE category_id = p.category_id
);

40. ALL с датами, отфильтрованными по условию:

SELECT * FROM schedules
WHERE start_time > ALL (
  SELECT end_time FROM sessions WHERE status = 'closed'
);

📆 Заключение: зачем использовать ALL в Oracle SQL

Оператор ALL — это способ писать строгие фильтры и проводить логические сравнения с наборами данных. Он незаменим, когда требуется проверить, что значение больше, меньше или отличается от всех других. Понимание его поведения особенно важно при работе с подзапросами и NULL.

🕸️ Следующая статья:

FETCH в PL/SQL — как извлекать строки из курсора