ANY в Oracle SQL: как сравнивать значение с множеством

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

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

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

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

• оператор — =, !=, >, <, >=, <=
• подзапрос должен возвращать только один столбец

Пример:

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

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

• Сравнение значения с множеством
• Фильтрация «больше хотя бы одного», «меньше хотя бы одного»
• Работа в подзапросах и вложенных условиях
• Оптимизация логики вместо множественных OR

📊 Примеры использования ANY в Oracle SQL (1–50 из 50)

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

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

2. Проверка даты:

SELECT * FROM orders
WHERE order_date < ANY (SELECT delivery_date FROM shipments);

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

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

4. Использование = ANY (эквивалент IN):

SELECT * FROM employees
WHERE job_id = ANY (SELECT job_id FROM jobs WHERE department_id = 20);

5. NOT = ANY (эквивалент NOT IN):

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

6. ANY с BETWEEN:

SELECT * FROM employees
WHERE salary BETWEEN 3000 AND ANY (SELECT salary FROM employees WHERE commission_pct > 0);

7. Сравнение чисел:

SELECT * FROM products
WHERE quantity > ANY (SELECT quantity FROM inventory WHERE warehouse_id = 2);

8. ANY с TO_DATE:

SELECT * FROM events
WHERE event_date < ANY (
  SELECT TO_DATE('2025-12-01', 'YYYY-MM-DD') FROM dual
);

9. ANY в подзапросе:

SELECT first_name FROM employees
WHERE employee_id = ANY (SELECT manager_id FROM employees);

10. Использование с NOT ANY (с NOT EXISTS):

SELECT * FROM customers
WHERE NOT EXISTS (
  SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id
);

11. Сравнение с агрегатной выборкой:

SELECT * FROM employees
WHERE commission_pct > ANY (
  SELECT AVG(commission_pct) FROM employees GROUP BY department_id
);

12. ANY с аналитикой:

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

13. Использование в EXISTS:

SELECT * FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e WHERE e.department_id = d.department_id AND e.salary > ANY (SELECT salary FROM employees WHERE department_id = 90)
);

14. Сравнение с JSON_TABLE:

SELECT * FROM logs
WHERE event_code = ANY (
  SELECT code FROM JSON_TABLE(:json, '$.events[*]' COLUMNS (code VARCHAR2(10) PATH '$.code'))
);

15. ANY с UNION:

SELECT * FROM employees
WHERE salary > ANY (
  SELECT salary FROM hr_employees
  UNION
  SELECT salary FROM contractors
);

16. ANY с вложенным подзапросом:

SELECT * FROM orders
WHERE amount > ANY (
  SELECT total FROM invoices WHERE status = 'PAID'
);

17. ANY в IN-условии:

SELECT * FROM employees
WHERE department_id IN (
  SELECT department_id FROM departments WHERE location_id = ANY (
    SELECT location_id FROM locations WHERE country_id = 'US'
  )
);

18. ANY и DATE сравнение:

SELECT * FROM meetings
WHERE start_time > ANY (SELECT schedule_time FROM calendars);

19. ANY в SELECT выражении:

SELECT first_name, (salary > ANY (SELECT salary FROM employees WHERE department_id = 90)) AS above_avg
FROM employees;

20. ANY и NOT LIKE:

SELECT * FROM customers
WHERE region NOT LIKE ANY (SELECT region_code FROM blocked_regions);

21. ANY с подзапросом по статусу:

SELECT * FROM tasks
WHERE priority > ANY (SELECT priority FROM tasks WHERE status = 'in_progress');

22. ANY с фильтрацией по email:

SELECT * FROM users
WHERE email LIKE ANY (SELECT email_pattern FROM allowed_patterns);

23. Сравнение sales с top_sales:

SELECT * FROM sales
WHERE amount > ANY (SELECT amount FROM sales WHERE category = 'top');

24. ANY с использованием IS NOT NULL:

SELECT * FROM documents
WHERE doc_id = ANY (SELECT id FROM uploads WHERE content IS NOT NULL);

25. Сравнение чисел через ALL и ANY:

SELECT * FROM benchmarks
WHERE score > ANY (SELECT threshold FROM configs) AND score < ALL (SELECT max_value FROM configs);

26. Сравнение статусов заявок:

SELECT * FROM applications
WHERE status = ANY (SELECT status_code FROM statuses WHERE type = 'open');

27. ANY в списке задач по проекту:

SELECT * FROM project_tasks
WHERE project_id = ANY (SELECT id FROM projects WHERE manager_id = 12);

28. ANY в сравнении пользователей по ID:

SELECT * FROM activity_log
WHERE user_id = ANY (SELECT id FROM users WHERE last_login IS NOT NULL);

29. ANY в логике блокировки:

SELECT * FROM sessions
WHERE ip_address = ANY (SELECT ip FROM blacklist);

30. ANY и математическое сравнение:

SELECT * FROM performance
WHERE ratio >= ANY (SELECT ratio FROM metrics WHERE month = 'DEC');

31. ANY и CASE WHEN:

SELECT CASE WHEN salary > ANY (SELECT salary FROM employees WHERE department_id = 90)
            THEN 'High'
            ELSE 'Normal'
       END AS status FROM employees;

32. ANY с вложенным WHERE:

SELECT * FROM projects
WHERE budget > ANY (
  SELECT budget FROM projects WHERE year = 2022 AND status = 'active'
);

33. ANY в подзапросе SELECT:

SELECT name, (score >= ANY (SELECT score FROM ratings)) AS passed FROM exams;

34. ANY и отрицание условия:

SELECT * FROM reviews
WHERE rating NOT IN (SELECT rating FROM banned_values);

35. ANY с датой окончания:

SELECT * FROM trainings
WHERE end_date > ANY (SELECT end_date FROM trainings WHERE status = 'archived');

36. ANY в подзапросе SELECT COUNT:

SELECT COUNT(*) FROM users
WHERE age >= ANY (SELECT age FROM users WHERE country = 'UK');

37. ANY и динамическое условие:

SELECT * FROM inventory
WHERE category_id = ANY (SELECT id FROM categories WHERE active = 'Y');

38. ANY для сравнения по среднему:

SELECT * FROM products
WHERE rating > ANY (SELECT AVG(rating) FROM products GROUP BY category_id);

39. ANY с IN и NOT IN:

SELECT * FROM assets
WHERE asset_type IN (SELECT type FROM types WHERE active = 'Y')
AND asset_id NOT IN (SELECT id FROM archived_assets);

40. ANY и привязка к месту:

SELECT * FROM events
WHERE location_id = ANY (SELECT id FROM locations WHERE region = 'East');

41. ANY и JSON:

SELECT * FROM notifications
WHERE topic = ANY (
  SELECT value FROM JSON_TABLE(:json_payload, '$.topics[*]' COLUMNS (value VARCHAR2(50) PATH '$'))
);

42. ANY в EXISTS подзапросе:

SELECT * FROM students s
WHERE EXISTS (
  SELECT 1 FROM exams e WHERE e.student_id = s.id AND e.score > ANY (SELECT score FROM exams WHERE subject = 'Math')
);

43. ANY с HAVING:

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > ANY (SELECT salary FROM employees WHERE job_id = 'CLERK');

44. ANY и UNION ALL:

SELECT * FROM messages
WHERE sender_id = ANY (
  SELECT user_id FROM sent_messages
  UNION ALL
  SELECT user_id FROM received_messages
);

45. ANY в WHERE c агрегацией:

SELECT * FROM suppliers
WHERE rating < ANY (SELECT MAX(rating) FROM suppliers GROUP BY region);

46. ANY для выбора лучших строк:

SELECT * FROM photos
WHERE likes > ANY (SELECT likes FROM photos WHERE category = 'landscape');

47. ANY с нумерацией строк:

SELECT * FROM leaderboard
WHERE rank <= ANY (SELECT rank FROM leaderboard WHERE player_id IN (1001, 1002));

48. ANY с NULL-фильтрацией:

SELECT * FROM support_requests
WHERE agent_id = ANY (SELECT id FROM agents WHERE is_active = 'Y' AND region IS NOT NULL);

49. ANY и NOT LIKE:

SELECT * FROM logs
WHERE action NOT LIKE ANY (SELECT pattern FROM suspicious_patterns);

50. ANY в вычисляемом столбце:

SELECT username, CASE WHEN login_count > ANY (SELECT login_count FROM users WHERE active = 'Y') THEN 'Frequent' ELSE 'Normal' END AS activity_level
FROM users;

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

Оператор ANY даёт гибкость при сравнении значения с подмножеством, особенно когда интересует один или более совпадений. Это эффективная замена множественным OR, особенно в подзапросах и динамических условиях.

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

UNION в Oracle SQL — как объединять результаты нескольких запросов