OR в Oracle SQL — 50 примеров условий по выбору

OR в Oracle SQL — это логический оператор, который позволяет задать условие, при котором достаточно выполнения хотя бы одного из перечисленных. В этой статье ты научишься применять OR в простых и сложных условиях, комбинировать его с AND, NOT, подзапросами, списками, LIKE, BETWEEN и др. Приведены 50 полезных примеров.

🖋️ Синтаксис логического оператора OR

SELECT [столбцы | *]
FROM [таблица]
WHERE условие1 OR условие2 [...]

Оператор OR объединяет условия так, что если хотя бы одно из них возвращает TRUE — строка включается в результат.

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

• Альтернативные фильтры
• Поиск по нескольким значениям
• Списки условий
• Совмещение с LIKE и IN
• Обработка данных с разными сценариями

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

1. Два условия:

SELECT * FROM employees WHERE job_id = 'IT_PROG' OR department_id = 30;

2. Три условия:

SELECT * FROM employees WHERE job_id = 'HR' OR job_id = 'SA_MAN' OR department_id = 50;

3. OR с числовыми условиями:

SELECT * FROM employees WHERE salary < 3000 OR salary > 9000;

4. OR с LIKE:

SELECT * FROM employees WHERE first_name LIKE 'A%' OR first_name LIKE 'B%';

5. OR с BETWEEN:

SELECT * FROM employees WHERE salary BETWEEN 2000 AND 3000 OR salary BETWEEN 8000 AND 9000;

6. OR с NULL:

SELECT * FROM employees WHERE manager_id IS NULL OR commission_pct IS NULL;

7. OR с NOT IN:

SELECT * FROM employees WHERE job_id NOT IN ('IT_PROG') OR department_id = 50;

8. OR с подзапросом:

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

9. OR с REGEXP_LIKE:

SELECT * FROM employees WHERE REGEXP_LIKE(first_name, '^A') OR REGEXP_LIKE(first_name, '^B');

10. OR в UPDATE:

UPDATE employees SET salary = salary + 500 WHERE department_id = 60 OR job_id = 'SA_MAN';

11. OR в DELETE:

DELETE FROM employees WHERE hire_date < TO_DATE('2005-01-01', 'YYYY-MM-DD') OR job_id = 'HR';

12. OR в MERGE:

MERGE INTO employees e USING new_employees n
ON (e.employee_id = n.employee_id OR e.email = n.email)
WHEN MATCHED THEN UPDATE SET e.salary = n.salary;

13. OR с функциями:

SELECT * FROM employees WHERE LENGTH(first_name) < 4 OR UPPER(last_name) LIKE 'S%';

14. OR с IN:

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

15. OR с AND (в скобках):

SELECT * FROM employees WHERE (job_id = 'HR' AND department_id = 60) OR salary > 9000;

16. OR с INTERVAL:

SELECT * FROM employees WHERE hire_date < SYSDATE - INTERVAL '10' YEAR OR salary < 3000;

17. OR с TO_CHAR:

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

18. OR в WITH-CTE:

WITH selected AS (
  SELECT * FROM employees WHERE department_id = 50 OR job_id = 'SA_REP'
)
SELECT * FROM selected;

19. OR с аналитикой:

SELECT * FROM (
  SELECT *, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
) WHERE rnk = 1 OR rnk = 2;

20. OR с UNION:

SELECT * FROM employees WHERE department_id = 10
UNION
SELECT * FROM employees WHERE department_id = 20 OR job_id = 'SA_REP';

21. OR с NVL:

SELECT * FROM employees WHERE NVL(commission_pct, 0) = 0 OR manager_id IS NULL;

22. OR с JSON_VALUE:

SELECT * FROM employees_json WHERE JSON_VALUE(data, '$.region') = 'North' OR JSON_VALUE(data, '$.region') = 'South';

23. OR в VIEW:

CREATE OR REPLACE VIEW v_filter AS
SELECT * FROM employees WHERE department_id = 50 OR job_id = 'HR';

24. OR в подзапросе:

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

25. OR с SUBSTR:

SELECT * FROM employees WHERE SUBSTR(first_name, 1, 1) = 'A' OR SUBSTR(last_name, 1, 1) = 'B';

26. OR с COALESCE:

SELECT * FROM employees WHERE COALESCE(manager_id, 0) = 0 OR department_id = 90;

27. OR с CASE:

SELECT * FROM employees WHERE
  CASE WHEN department_id = 10 THEN 'Y' ELSE 'N' END = 'Y'
  OR salary > 8000;

28. OR с LOWER:

SELECT * FROM employees WHERE LOWER(first_name) = 'john' OR LOWER(first_name) = 'mary';

29. OR с модулем MOD:

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

30. OR с ROUND:

SELECT * FROM employees WHERE ROUND(salary, -3) = 5000 OR ROUND(salary, -3) = 10000;

31. OR с NULLIF:

SELECT * FROM employees WHERE NULLIF(salary, 0) = salary OR salary = 0;

32. OR в DELETE с EXISTS:

DELETE FROM employees e WHERE EXISTS (
  SELECT 1 FROM job_history j
  WHERE j.employee_id = e.employee_id AND j.department_id = 50
) OR e.salary < 2000;

33. OR в UPDATE с подзапросом:

UPDATE employees SET job_id = 'TEMP'
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'HR')
OR manager_id = 100;

34. OR с IS NOT NULL:

SELECT * FROM employees WHERE commission_pct IS NOT NULL OR manager_id IS NOT NULL;

35. OR в GROUP BY + HAVING:

SELECT department_id, COUNT(*) FROM employees
GROUP BY department_id
HAVING COUNT(*) < 5 OR AVG(salary) > 10000;

36. OR с SYS_CONTEXT:

SELECT * FROM employees WHERE department_id = 70 OR SYS_CONTEXT('USERENV', 'SESSION_USER') = 'HR';

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

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

38. OR в SELECT внутри SELECT:

SELECT first_name, (SELECT job_id FROM job_history WHERE job_id = 'HR' OR job_id = 'SA_MAN' AND ROWNUM = 1)
FROM employees;

39. OR с CASE и датой:

SELECT * FROM employees WHERE 
  CASE WHEN hire_date < SYSDATE - 365 THEN 'OLD' ELSE 'NEW' END = 'OLD'
  OR salary > 8000;

40. OR в DELETE по фильтру:

DELETE FROM employees WHERE salary < 2000 OR job_id = 'ST_CLERK';

41. OR с CUBE:

SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY CUBE(department_id, job_id)
HAVING job_id IS NULL OR department_id IS NULL;

42. OR с JSON_TABLE:

SELECT * FROM JSON_TABLE(
  '{"employees": [{"id":1,"role":"HR"},{"id":2,"role":"IT"}]}'
  , '$.employees[*]'
  COLUMNS (id NUMBER PATH '$.id', role VARCHAR2(10) PATH '$.role')
) WHERE role = 'HR' OR role = 'IT';

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

SELECT * FROM mv_employees WHERE department_id = 10 OR salary > 7000;

44. OR с аналитикой RATIO_TO_REPORT:

SELECT * FROM (
  SELECT department_id, salary, RATIO_TO_REPORT(salary) OVER (PARTITION BY department_id) AS ratio
  FROM employees
) WHERE ratio > 0.2 OR salary > 8000;

45. OR с dual:

SELECT 'ok' FROM dual WHERE 1=1 OR 1=0;

46. OR с REPLACE:

SELECT * FROM employees WHERE REPLACE(first_name, 'a', 'A') = 'Alen' OR REPLACE(last_name, 'e', 'E') = 'Smith';

47. OR с скалярной подстановкой:

SELECT * FROM employees WHERE department_id = (SELECT MAX(department_id) FROM departments) OR job_id = 'AD_PRES';

48. OR с аналитической фильтрацией RANK:

SELECT * FROM (
  SELECT *, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
) WHERE rnk <= 2 OR job_id = 'IT_PROG';

49. OR с DUMP и строкой:

SELECT * FROM employees WHERE DUMP(job_id) LIKE '%73%' OR job_id = 'AD_ASST';

50. OR с фильтром по длине строки:

SELECT * FROM employees WHERE LENGTH(first_name) = 4 OR LENGTH(last_name) = 6;

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

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

Зная OR, ты сможешь создавать условия с выбором, реализовывать проверку альтернатив и упрощать структуру WHERE.

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

NOT в Oracle SQL — как исключать ненужные строки