EXISTS в Oracle SQL — как проверять наличие подзапроса

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

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

SELECT * FROM таблица
WHERE EXISTS (
  SELECT 1 FROM другая_таблица WHERE условие
);

Также используется:

• NOT EXISTS — возвращает TRUE, если подзапрос не возвращает ни одной строки

🔄 Где используется EXISTS

• Проверка наличия связанных записей
• Вложенные фильтрации
• Условия с динамическими подзапросами
• Работа с коррелированными запросами

📊 Примеры использования EXISTS в Oracle SQL

1. EXISTS с простой связью:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d WHERE d.department_id = e.department_id
);

2. NOT EXISTS для отсутствия связи:

SELECT * FROM departments d
WHERE NOT EXISTS (
  SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);

3. EXISTS с подзапросом по фильтру:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM job_history j WHERE j.employee_id = e.employee_id AND j.department_id = 80
);

4. EXISTS внутри SELECT:

SELECT first_name,
  CASE WHEN EXISTS (
    SELECT 1 FROM job_history j WHERE j.employee_id = e.employee_id
  ) THEN 'Есть история' ELSE 'Нет истории' END AS история
FROM employees e;

5. EXISTS в DELETE:

DELETE FROM employees e
WHERE EXISTS (
  SELECT 1 FROM job_history j WHERE j.employee_id = e.employee_id
);

6. EXISTS с фильтрацией зарплаты:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM employees e2 WHERE e2.salary > 10000 AND e2.employee_id = e.employee_id
);

7. EXISTS с подзапросом на подчинённых:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM employees sub WHERE sub.manager_id = e.employee_id
);

8. EXISTS с датами:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM job_history j WHERE j.employee_id = e.employee_id AND j.start_date < TO_DATE('2022-01-01', 'YYYY-MM-DD')
);

9. EXISTS с JOIN внутри подзапроса:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d JOIN locations l ON d.location_id = l.location_id
  WHERE d.department_id = e.department_id AND l.city = 'Seattle'
);

10. EXISTS в UPDATE:

UPDATE employees e SET salary = salary * 1.05
WHERE EXISTS (
  SELECT 1 FROM job_history j WHERE j.employee_id = e.employee_id AND j.department_id = 80
);

11. EXISTS с вложенными подзапросами:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d
  WHERE d.department_id = (
    SELECT department_id FROM job_history j WHERE j.employee_id = e.employee_id AND ROWNUM = 1
  )
);

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

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM (
    SELECT employee_id, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
  ) ranked WHERE ranked.employee_id = e.employee_id AND rnk <= 5
);

13. EXISTS и подстановка строки:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d WHERE d.department_name LIKE 'Sal%' AND d.department_id = e.department_id
);

14. EXISTS с JSON_VALUE:

SELECT * FROM employees_json ej
WHERE EXISTS (
  SELECT 1 FROM dual WHERE JSON_VALUE(ej.data, '$.status') = 'active'
);

15. EXISTS с фильтрацией по NULL:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM job_history j WHERE j.employee_id = e.employee_id AND j.end_date IS NULL
);

16. EXISTS с коррелированным подзапросом по дате:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM job_history j WHERE j.employee_id = e.employee_id AND j.start_date > e.hire_date
);

17. EXISTS в SELECT внутри SELECT:

SELECT employee_id,
  CASE WHEN EXISTS (
    SELECT 1 FROM job_history j WHERE j.employee_id = e.employee_id
  ) THEN 'Работал раньше' ELSE 'Нет данных' END AS статус
FROM employees e;

18. EXISTS в фильтрации агрегатов:

SELECT department_id FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e WHERE e.department_id = d.department_id
GROUP BY e.department_id HAVING COUNT(*) > 5
);

19. EXISTS с ROWNUM:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM job_history j WHERE j.employee_id = e.employee_id AND ROWNUM = 1
);

20. EXISTS с функцией UPPER:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d WHERE UPPER(d.department_name) = 'IT' AND d.department_id = e.department_id
);

21. EXISTS с подзапросом на DISTINCT:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT DISTINCT department_id FROM job_history j WHERE j.employee_id = e.employee_id
);

22. EXISTS с подзапросом WHERE IN:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d WHERE d.department_id IN (10, 20, 30) AND d.department_id = e.department_id
);

23. EXISTS с JSON_TABLE:

SELECT * FROM JSON_TABLE(
  '{"employees": [{"id":1,"status":"active"},{"id":2,"status":"inactive"}]}'
  , '$.employees[*]'
  COLUMNS (id NUMBER PATH '$.id', status VARCHAR2(20) PATH '$.status')
) WHERE EXISTS (
  SELECT 1 FROM dual WHERE status = 'active'
);

24. EXISTS с VIEW:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM v_active_employees v WHERE v.employee_id = e.employee_id
);

25. EXISTS в PL/SQL:

DECLARE
  v_exists BOOLEAN;
BEGIN
  SELECT CASE WHEN EXISTS (
    SELECT 1 FROM employees WHERE department_id = 50
  ) THEN TRUE ELSE FALSE END INTO v_exists FROM dual;
END;

26. EXISTS с подзапросом IN SELECT:

SELECT employee_id FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d WHERE d.department_id IN (
    SELECT department_id FROM job_history WHERE employee_id = e.employee_id
  )
);

27. EXISTS с аналитикой RANK:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM (
    SELECT employee_id, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
  ) ranked WHERE ranked.rnk <= 3 AND ranked.employee_id = e.employee_id
);

28. EXISTS с CASE в подзапросе:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM job_history j
  WHERE j.employee_id = e.employee_id
  AND CASE WHEN j.department_id = 60 THEN 1 ELSE 0 END = 1
);

29. EXISTS с логикой AND и OR:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d
  WHERE d.department_id = e.department_id AND (d.location_id = 1700 OR d.location_id = 1800)
);

30. EXISTS с TO_CHAR:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM job_history j WHERE j.employee_id = e.employee_id
  AND TO_CHAR(j.start_date, 'YYYY') = '2022'
);

31. EXISTS с INITCAP:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d WHERE INITCAP(d.department_name) = 'Finance'
  AND d.department_id = e.department_id
);

32. EXISTS в аналитике MEDIAN:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM (
    SELECT employee_id, MEDIAN(salary) OVER () AS med FROM employees
  ) m WHERE m.salary >= med AND m.employee_id = e.employee_id
);

33. EXISTS с аналитикой PERCENT_RANK:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM (
    SELECT employee_id, PERCENT_RANK() OVER (ORDER BY salary) AS p FROM employees
  ) x WHERE p < 0.5 AND x.employee_id = e.employee_id
);

34. EXISTS с аналитикой NTILE:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM (
    SELECT employee_id, NTILE(4) OVER (ORDER BY hire_date) AS tile FROM employees
  ) x WHERE tile = 1 AND x.employee_id = e.employee_id
);

35. EXISTS с внешней таблицей:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM external_employees ex WHERE ex.employee_id = e.employee_id
);

36. EXISTS с EXTRACT:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM job_history j WHERE EXTRACT(YEAR FROM j.start_date) = 2021 AND j.employee_id = e.employee_id
);

37. EXISTS с NVL:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM job_history j WHERE NVL(j.department_id, 0) = 90 AND j.employee_id = e.employee_id
);

38. EXISTS и LENGTH:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d WHERE LENGTH(d.department_name) > 5 AND d.department_id = e.department_id
);

39. EXISTS и TRIM:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d WHERE TRIM(d.department_name) = 'Finance'
  AND d.department_id = e.department_id
);

40. EXISTS с GROUPING SETS:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM (
    SELECT department_id FROM employees
    GROUP BY GROUPING SETS ((department_id))
  ) g WHERE g.department_id = e.department_id
);

41. EXISTS с MERGE:

MERGE INTO employees e USING dual d
ON (EXISTS (
  SELECT 1 FROM job_history j WHERE j.employee_id = e.employee_id
))
WHEN MATCHED THEN UPDATE SET salary = salary + 100;

42. EXISTS и RATIO_TO_REPORT:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM (
    SELECT employee_id, RATIO_TO_REPORT(salary) OVER () AS r FROM employees
  ) x WHERE r > 0.05 AND x.employee_id = e.employee_id
);

43. EXISTS и NULLIF:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM job_history j WHERE NULLIF(j.department_id, 0) = 80 AND j.employee_id = e.employee_id
);

44. EXISTS и DUMP:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d WHERE DUMP(d.department_name) LIKE '%70%' AND d.department_id = e.department_id
);

45. EXISTS и CASE в подзапросе:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM job_history j WHERE j.employee_id = e.employee_id
  AND CASE WHEN j.department_id = 100 THEN 1 ELSE 0 END = 1
);

46. EXISTS с INLINE VIEW:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM (
    SELECT department_id FROM departments WHERE location_id = 1700
  ) sub WHERE sub.department_id = e.department_id
);

47. EXISTS с UNPIVOT:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM (
    SELECT * FROM departments
    UNPIVOT (val FOR col IN (location_id, manager_id))
  ) u WHERE u.val = e.department_id
);

48. EXISTS с двойным EXISTS:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND
    EXISTS (
      SELECT 1 FROM locations l WHERE l.location_id = d.location_id AND l.country_id = 'US'
    )
);

49. EXISTS с UNION ALL:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM (
    SELECT department_id FROM job_history WHERE department_id = 80
    UNION ALL
    SELECT department_id FROM departments WHERE location_id = 1700
  ) combined WHERE combined.department_id = e.department_id
);

50. EXISTS с аналитикой и фильтрацией:

SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM (
    SELECT employee_id, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM employees
  ) ranked WHERE ranked.employee_id = e.employee_id AND rnk <= 3
);

📆 Заключение: зачем изучать EXISTS в Oracle SQL

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

Зная EXISTS, ты сможешь эффективно контролировать наличие данных, строить вложенные фильтры и создавать более читаемые и оптимальные запросы.

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

NULL в Oracle SQL — как работать с пустыми значениями