IN в Oracle SQL — 50 примеров проверки в списке

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

IN в Oracle SQL — оператор, который проверяет, входит ли значение в указанный список. Он позволяет упростить записи условий и сделать код читаемым. В этой статье ты увидишь, как использовать IN с числами, строками, датами, подзапросами и вложенными условиями. Примеры охватывают SELECT, UPDATE, DELETE и подзапросы.

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

SELECT [столбцы | *]
FROM [таблица]
WHERE выражение IN (значение1, значение2, ...)

Можно также использовать IN с подзапросом:

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

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

• Проверка принадлежности к нескольким значениям
• Замена множественных OR
• Подзапросы
• Применение в фильтрах, обновлении, удалении

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

1. IN с числами:

SELECT * FROM employees WHERE department_id IN (10, 20, 30);

2. IN со строками:

SELECT * FROM employees WHERE job_id IN ('IT_PROG', 'HR', 'SA_MAN');

3. IN с датами:

SELECT * FROM employees WHERE hire_date IN (TO_DATE('2020-01-01', 'YYYY-MM-DD'), TO_DATE('2022-01-01', 'YYYY-MM-DD'));

4. IN с подзапросом:

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

5. IN в UPDATE:

UPDATE employees SET salary = salary * 1.1 WHERE department_id IN (50, 60);

6. IN в DELETE:

DELETE FROM employees WHERE department_id IN (70, 80);

7. IN с функцией UPPER:

SELECT * FROM employees WHERE UPPER(job_id) IN ('HR', 'IT_PROG');

8. IN с функцией TO_CHAR:

SELECT * FROM employees WHERE TO_CHAR(hire_date, 'YYYY') IN ('2020', '2021');

9. IN с функцией LENGTH:

SELECT * FROM employees WHERE LENGTH(first_name) IN (3, 5, 7);

10. IN с SUBSTR:

SELECT * FROM employees WHERE SUBSTR(last_name, 1, 1) IN ('A', 'B');

11. IN с COALESCE:

SELECT * FROM employees WHERE COALESCE(manager_id, 0) IN (100, 101);

12. IN с подзапросом по job_history:

SELECT * FROM employees WHERE employee_id IN (
  SELECT employee_id FROM job_history WHERE job_id = 'SA_REP'
);

13. IN с INLINE VIEW:

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

14. IN в аналитическом подзапросе:

SELECT * FROM (
  SELECT employee_id, department_id, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM employees
) WHERE rnk IN (1, 2);

15. IN с JSON_VALUE:

SELECT * FROM employees_json WHERE JSON_VALUE(data, '$.region') IN ('West', 'East');

16. IN с SYS_CONTEXT:

SELECT * FROM employees WHERE SYS_CONTEXT('USERENV', 'SESSION_USER') IN ('HR', 'SCOTT');

17. IN с IS NULL не работает напрямую:

-- Этот запрос ничего не вернёт:
SELECT * FROM employees WHERE commission_pct IN (NULL);

18. IN с датой и преобразованием:

SELECT * FROM employees WHERE TO_DATE(TO_CHAR(hire_date, 'YYYY-MM-DD'), 'YYYY-MM-DD') IN (
  TO_DATE('2021-01-01', 'YYYY-MM-DD')
);

19. IN с MOD:

SELECT * FROM employees WHERE MOD(salary, 1000) IN (0, 500);

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

SELECT * FROM (
  SELECT employee_id, department_id, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
) WHERE dr IN (1, 2);

21. IN в MERGE:

MERGE INTO employees e USING new_employees n
ON (e.employee_id IN (100, 101))
WHEN MATCHED THEN UPDATE SET e.salary = n.salary;

22. IN с REPLACE:

SELECT * FROM employees WHERE REPLACE(first_name, 'e', 'E') IN ('JoEl', 'StEvE');

23. IN в WITH:

WITH sales_depts AS (
  SELECT department_id FROM departments WHERE department_name LIKE 'Sales%'
)
SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM sales_depts);

24. IN с GROUP BY + HAVING:

SELECT department_id FROM employees
GROUP BY department_id
HAVING COUNT(*) IN (2, 3);

25. IN с материализованным представлением:

SELECT * FROM mv_employees WHERE department_id IN (10, 20);

26. IN с аналитикой и CASE:

SELECT * FROM (
  SELECT employee_id, department_id,
         CASE WHEN salary > 5000 THEN 'HIGH' ELSE 'LOW' END AS level
  FROM employees
) WHERE level IN ('HIGH');

27. IN с NULLIF:

SELECT * FROM employees WHERE NULLIF(salary, 0) IN (3000, 5000);

28. IN с 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 status IN ('active');

29. IN с CAST:

SELECT * FROM employees WHERE CAST(salary AS INTEGER) IN (4000, 5000);

30. IN с ROWNUM:

SELECT * FROM (
  SELECT * FROM employees WHERE ROWNUM <= 10
) WHERE department_id IN (90);

31. IN с аналитикой и HAVING:

SELECT department_id, COUNT(*) FROM employees
GROUP BY department_id
HAVING COUNT(*) IN (1, 5);

32. IN с параметрами для PL/SQL:

-- Использование IN в PL/SQL блоке
BEGIN
  FOR emp IN (
    SELECT * FROM employees WHERE job_id IN ('IT_PROG', 'SA_MAN')
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(emp.first_name);
  END LOOP;
END;

33. IN в подзапросе WHERE EXISTS:

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

34. IN с аналитикой и RANK:

SELECT * FROM (
  SELECT employee_id, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
) WHERE rnk IN (1, 2, 3);

35. IN с UNION ALL:

SELECT * FROM employees WHERE department_id IN (10, 20)
UNION ALL
SELECT * FROM employees WHERE job_id IN ('HR', 'IT_PROG');

36. IN с CONCAT:

SELECT * FROM employees WHERE first_name || last_name IN ('JohnSmith', 'JaneDoe');

37. IN с аналитикой и ROW_NUMBER:

SELECT * FROM (
  SELECT employee_id, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees
) WHERE rn IN (1, 2, 3);

38. IN с GROUPING SETS:

SELECT department_id, job_id, COUNT(*) FROM employees
GROUP BY GROUPING SETS ((department_id), (job_id))
HAVING department_id IN (10, 20) OR job_id IN ('HR', 'IT_PROG');

39. IN с DUMP:

SELECT * FROM employees WHERE DUMP(job_id) IN ('Typ=1 Len=6: 73,65,95,77,65,78');

40. IN с ROLLUP:

SELECT department_id, SUM(salary) FROM employees
GROUP BY ROLLUP (department_id)
HAVING department_id IN (10, 20);

41. IN с DECODE:

SELECT * FROM employees WHERE DECODE(department_id, 10, 'Y', 'N') IN ('Y');

42. IN с INTERVAL:

SELECT * FROM employees WHERE SYSDATE - hire_date IN (INTERVAL '365' DAY);

43. IN с PIVOT:

SELECT * FROM (
  SELECT department_id, job_id, salary FROM employees
)
PIVOT (SUM(salary) FOR job_id IN ('IT_PROG', 'HR'))
WHERE department_id IN (60);

44. IN с фильтрацией дат:

SELECT * FROM employees WHERE hire_date IN (
  SELECT DISTINCT hire_date FROM job_history
);

45. IN с аналитикой и AVG:

SELECT * FROM (
  SELECT employee_id, salary, AVG(salary) OVER () AS avg_sal FROM employees
) WHERE salary IN (avg_sal);

46. IN с внешними таблицами:

SELECT * FROM external_employees WHERE department_id IN (10, 20);

47. IN с фильтрацией строк длиной:

SELECT * FROM employees WHERE LENGTH(first_name) IN (4, 5);

48. IN в DML RETURNING:

DECLARE
  v_job VARCHAR2(20);
BEGIN
  DELETE FROM employees WHERE job_id IN ('HR') RETURNING job_id INTO v_job;
END;

49. IN с функцией INITCAP:

SELECT * FROM employees WHERE INITCAP(first_name) IN ('John', 'Jane');

50. IN с фильтрацией по NULLIF:

SELECT * FROM employees WHERE NULLIF(department_id, 0) IN (10, 20);

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

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

Зная IN, ты сможешь легко исключать множественные OR, фильтровать по спискам и строить эффективные подзапросы.

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

IF в Oracle SQL — как выполнять условную логику в PL/SQL