BETWEEN в Oracle SQL — 50 примеров поиска в диапазоне

BETWEEN в Oracle SQL — оператор, позволяющий проверять, попадает ли значение в указанный диапазон. Он упрощает условия выборки для чисел, дат и строк. Эта статья покажет, как использовать BETWEEN в SELECT, WHERE, UPDATE, подзапросах и с функциями. Всё с примерами.

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

SELECT [столбцы | *]
FROM [таблица]
WHERE выражение BETWEEN значение1 AND значение2;

Также можно использовать NOT BETWEEN для исключения значений:

SELECT * FROM employees WHERE salary NOT BETWEEN 3000 AND 6000;

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

• Проверка числовых и временных диапазонов
• Упрощение условий WHERE
• Выбор строк с фильтром по дате или диапазону
• Обновление и удаление по интервалу

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

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

SELECT * FROM employees WHERE salary BETWEEN 3000 AND 6000;

2. BETWEEN с датами:

SELECT * FROM employees WHERE hire_date BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD') AND SYSDATE;

3. BETWEEN с строками:

SELECT * FROM employees WHERE last_name BETWEEN 'A' AND 'M';

4. NOT BETWEEN:

SELECT * FROM employees WHERE salary NOT BETWEEN 7000 AND 9000;

5. BETWEEN в UPDATE:

UPDATE employees SET salary = salary + 500 WHERE salary BETWEEN 4000 AND 5000;

6. BETWEEN с LENGTH:

SELECT * FROM employees WHERE LENGTH(first_name) BETWEEN 3 AND 6;

7. BETWEEN с TO_CHAR:

SELECT * FROM employees WHERE TO_CHAR(hire_date, 'YYYY') BETWEEN '2019' AND '2022';

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

SELECT * FROM employees WHERE salary BETWEEN (
  SELECT MIN(salary) FROM employees
) AND (
  SELECT MAX(salary) FROM employees
);

9. BETWEEN с функцией INITCAP:

SELECT * FROM employees WHERE INITCAP(first_name) BETWEEN 'Anna' AND 'Maria';

10. BETWEEN в DELETE:

DELETE FROM employees WHERE department_id BETWEEN 40 AND 60;

11. BETWEEN с датой и INTERVAL:

SELECT * FROM employees WHERE hire_date BETWEEN SYSDATE - INTERVAL '2' YEAR AND SYSDATE;

12. BETWEEN с числом и выражением:

SELECT * FROM employees WHERE salary BETWEEN 2000 AND salary + 3000;

13. BETWEEN в WITH:

WITH avg_salaries AS (
  SELECT * FROM employees WHERE salary BETWEEN 4000 AND 6000
)
SELECT * FROM avg_salaries;

14. BETWEEN в MERGE:

MERGE INTO employees e USING new_employees n
ON (e.employee_id = n.employee_id AND e.salary BETWEEN 3000 AND 5000)
WHEN MATCHED THEN UPDATE SET e.salary = n.salary;

15. BETWEEN в подзапросе:

SELECT * FROM employees WHERE department_id IN (
  SELECT department_id FROM departments WHERE location_id BETWEEN 1700 AND 1800
);

16. NOT BETWEEN с датами:

SELECT * FROM employees WHERE hire_date NOT BETWEEN TO_DATE('2000-01-01','YYYY-MM-DD') AND TO_DATE('2010-01-01','YYYY-MM-DD');

17. BETWEEN с SUBSTR:

SELECT * FROM employees WHERE SUBSTR(first_name, 1, 1) BETWEEN 'A' AND 'K';

18. BETWEEN с SYSDATE:

SELECT * FROM employees WHERE hire_date BETWEEN SYSDATE - 100 AND SYSDATE;

19. BETWEEN с модулем MOD:

SELECT * FROM employees WHERE MOD(salary, 1000) BETWEEN 100 AND 500;

20. BETWEEN в аналитике:

SELECT * FROM (
  SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
) WHERE rnk BETWEEN 1 AND 5;

21. BETWEEN с функцией REPLACE:

SELECT * FROM employees WHERE REPLACE(first_name, 'a', 'A') BETWEEN 'Alen' AND 'Steve';

22. BETWEEN в HAVING:

SELECT department_id, COUNT(*) FROM employees
GROUP BY department_id
HAVING COUNT(*) BETWEEN 2 AND 5;

23. BETWEEN с ROWNUM:

SELECT * FROM (
  SELECT * FROM employees ORDER BY hire_date
) WHERE ROWNUM BETWEEN 1 AND 10;

24. BETWEEN с JSON_VALUE:

SELECT * FROM employees_json WHERE JSON_VALUE(data, '$.experience') BETWEEN 2 AND 5;

25. BETWEEN с аналитикой DENSE_RANK:

SELECT * FROM (
  SELECT employee_id, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dr
  FROM employees
) WHERE dr BETWEEN 1 AND 3;

26. BETWEEN с TO_DATE и строкой:

SELECT * FROM employees WHERE TO_DATE(TO_CHAR(hire_date, 'YYYY-MM-DD'), 'YYYY-MM-DD') BETWEEN TO_DATE('2020-01-01','YYYY-MM-DD') AND TO_DATE('2022-12-31','YYYY-MM-DD');

27. BETWEEN с IS NOT NULL и датой:

SELECT * FROM employees WHERE hire_date IS NOT NULL AND hire_date BETWEEN TO_DATE('2015-01-01', 'YYYY-MM-DD') AND SYSDATE;

28. BETWEEN с аналитикой RANK:

SELECT * FROM (
  SELECT employee_id, RANK() OVER (PARTITION BY department_id ORDER BY hire_date ASC) AS rnk
  FROM employees
) WHERE rnk BETWEEN 1 AND 2;

29. BETWEEN с CONCAT:

SELECT * FROM employees WHERE first_name || last_name BETWEEN 'AnnSmith' AND 'TomWhite';

30. BETWEEN с внешней таблицей:

SELECT * FROM external_employees WHERE salary BETWEEN 2000 AND 8000;

31. BETWEEN с CAST:

SELECT * FROM employees WHERE CAST(salary AS INTEGER) BETWEEN 4000 AND 9000;

32. BETWEEN с CASE:

SELECT * FROM employees WHERE
  CASE WHEN salary BETWEEN 5000 AND 7000 THEN 'Y' ELSE 'N' END = 'Y';

33. BETWEEN с ROW_NUMBER:

SELECT * FROM (
  SELECT employee_id, ROW_NUMBER() OVER (ORDER BY hire_date DESC) AS rn FROM employees
) WHERE rn BETWEEN 1 AND 5;

34. BETWEEN с GROUPING SETS:

SELECT department_id, job_id, COUNT(*) FROM employees
GROUP BY GROUPING SETS ((department_id), (job_id))
HAVING COUNT(*) BETWEEN 2 AND 4;

35. BETWEEN с SYS_CONTEXT:

SELECT * FROM employees WHERE department_id BETWEEN 10 AND 90 AND SYS_CONTEXT('USERENV', 'SESSION_USER') = 'HR';

36. BETWEEN с аналитикой RATIO_TO_REPORT:

SELECT * FROM (
  SELECT department_id, salary, RATIO_TO_REPORT(salary) OVER (PARTITION BY department_id) AS ratio
  FROM employees
) WHERE ratio BETWEEN 0.1 AND 0.3;

37. BETWEEN с JSON_TABLE:

SELECT * FROM JSON_TABLE(
  '{"employees": [{"id":1,"score":3},{"id":2,"score":5}]}'
  , '$.employees[*]'
  COLUMNS (id NUMBER PATH '$.id', score NUMBER PATH '$.score')
) WHERE score BETWEEN 2 AND 5;

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

SELECT * FROM (
  SELECT employee_id, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM employees
) WHERE rnk BETWEEN 1 AND 3 AND department_id BETWEEN 50 AND 90;

39. BETWEEN в DELETE с подзапросом:

DELETE FROM employees WHERE department_id BETWEEN (
  SELECT MIN(department_id) FROM departments
) AND (
  SELECT MAX(department_id) FROM departments
);

40. BETWEEN с динамическим диапазоном:

SELECT * FROM employees WHERE salary BETWEEN salary - 1000 AND salary + 1000;

41. BETWEEN с NULLIF:

SELECT * FROM employees WHERE NULLIF(salary, 0) BETWEEN 3000 AND 6000;

42. BETWEEN с аналитикой AVG:

SELECT * FROM (
  SELECT employee_id, salary, AVG(salary) OVER () AS avg_sal FROM employees
) WHERE salary BETWEEN avg_sal - 1000 AND avg_sal + 1000;

43. BETWEEN с параметрами в PL/SQL:

DECLARE
  low NUMBER := 3000;
  high NUMBER := 6000;
BEGIN
  FOR emp IN (
    SELECT * FROM employees WHERE salary BETWEEN low AND high
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(emp.first_name);
  END LOOP;
END;

44. BETWEEN с аналитикой NTILE:

SELECT * FROM (
  SELECT employee_id, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees
) WHERE quartile BETWEEN 1 AND 2;

45. BETWEEN с SQL-функцией DUMP:

SELECT * FROM employees WHERE DUMP(job_id) BETWEEN 'Typ=1 Len=6:' AND 'Typ=1 Len=7:';

46. BETWEEN с DML RETURNING:

DECLARE
  v_id NUMBER;
BEGIN
  DELETE FROM employees WHERE department_id BETWEEN 10 AND 20 RETURNING employee_id INTO v_id;
END;

47. BETWEEN с INITCAP и строками:

SELECT * FROM employees WHERE INITCAP(last_name) BETWEEN 'Brown' AND 'White';

48. BETWEEN с аналитикой MEDIAN:

SELECT * FROM (
  SELECT employee_id, salary, MEDIAN(salary) OVER () AS med FROM employees
) WHERE salary BETWEEN med - 1000 AND med + 1000;

49. BETWEEN с UNION:

SELECT * FROM employees WHERE salary BETWEEN 3000 AND 6000
UNION
SELECT * FROM employees WHERE hire_date BETWEEN TO_DATE('2018-01-01','YYYY-MM-DD') AND TO_DATE('2020-12-31','YYYY-MM-DD');

50. BETWEEN с SQL-функцией TRUNC:

SELECT * FROM employees WHERE TRUNC(hire_date) BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-01-01', 'YYYY-MM-DD');

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

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

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

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

LIKE в Oracle SQL — как искать строки по шаблону