О. Ткачев - Основы программирования в СУБД Oracle. SQL+PL/SQL.

Синтаксис:

{имя столбца} BETWEEN V_MIN AND V_MAX

V_MIN – нижняя граница диапазона;

V_MAX – верхняя граница диапазона

Оператор BETWEEN осуществляет поиск среди всех значений диапазона, включая границы. Оператор BETWEEN эквивалентен двум операциям сравнения, объединенным логическим оператором AND.

({имя столбца}> = V_MIN) AND ({имя столбца} <= V_MAX)

Пример 2.16. Вывести данные о сотрудниках, зарплата которых находится в определенном диапазоне

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE salary BETWEEN 6000 AND 8000;

Для определения границ диапазона можно использовать вещественные числа, даты и строки.

Пример 2.17. Получить данные о сотрудниках, у которых значение комиссионных находится в определенном диапазоне

SELECT employee_id, first_name, last_name, department_id,

salary, commission_pct

FROM Employees

WHERE commission_pct BETWEEN 0.15 AND 0.2;

Пример 2.18. Получить данные о договорах, дата заключения которых лежит в определенном диапазоне

SELECT * FROM Orders

WHERE order_date BETWEEN ′ 01.09.2019 ′

AND ′ 30.09.2019 ′;

Оператор BETWEEN можно использовать совместно с логическим оператором NOT.

Пример 2.19. Получить данные о договорах, дата заключения которых не лежит в определенном диапазоне

SELECT * FROM Orders

WHERE order_date NOT BETWEEN ′ 01.09.2019 ′ AND ′ 30.09.2019 ′;

При использовании в качестве границ диапазона строчных значений нужно учитывать особенности сортировки строк. Например, нужно получить данные о сотрудниках, имена которых начинаются с букв в диапазоне с A по B.

На первый взгляд может показаться, что данную задачу должен решить следующий запрос.

Пример 2.20. Получить данные о сотрудниках, имена которых начинаются с букв в диапазоне с A по B (содержит ошибку)

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE first_name BETWEEN ′ A ′ AND ′ B ′;

Но анализ результатов этого запроса показывает, что данные о сотрудниках, чьи имена начинаются на букву B, в результат выполнения запроса не попали, хотя такие сотрудники есть, например Bruce.

Это происходит потому, что значение строки B меньше значения строки Bruce, поэтому данные о сотрудниках, чьи имена начинаются на букву B, в результат выполнения запроса не попали. Эту проблему можно решить, указывая в качестве верхнего диапазона следующую букву.

Пример 2.21. Получить данные о сотрудниках, имена которых начинаются с букв в диапазоне с A по B

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE first_name BETWEEN ′ A ′ AND ′ C ′;

Оператор IN

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

Синтаксис:

{имя столбца} IN {список значений}

Список значений в операторе IN может формироваться в результате выполнения оператора SELECT (подзапроса).

Пример 2.22. Вывести данные о сотрудниках, которые работают в отделах с определенными номерами

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE department_id IN (40, 10, 110);

Пример 2.23. Вывести данные о договорах, заключенных в определенные даты

SELECT * FROM Orders

WHERE order_date IN (′ 07.09.19 ′, ′ 14.09.19 ′, ′ 02.11.19 ′);

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

Пример 2.24. Вывести данные о сотрудниках, которые не работают в отделах с определенными номерами

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE department_id NOT IN (40, 10, 110);

Условия выбора, формируемые оператором IN, можно объединять с другими условиями выбора.

Пример 2.25. Вывести названия городов, которые расположены в США или Канаде и почтовый индекс которых заканчивается цифрой 2

SELECT city FROM Locations

WHERE (country_id IN (′ US ′, ′ CA ′))

AND (postal_code LIKE ′ %2 ′);

Следует иметь в виду, что если список значений в IN будет содержать NULL, то результат выполнения оператора не будет содержать строк, у которых проверяемый столбец имеет значение NULL, так как результат сравнения NULL имеет значение НЕ ОПРЕДЕЛЕНО (UNKNOWN).

Пример 2.26. Вывести данные о сотрудниках, которые работают в отделах с определенными номерами, и о сотрудниках, у которых не задан номер отдела

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE department_id IN (40, 10, 110, NULL);

При этом в таблице Employees есть строки, у которых столбец department имеет значение NULL (см. результаты выполнения запроса из примера 2.29).

Если список значений в NOT IN будет содержать NULL, то результат выполнения оператора SELECT будет пуст. Это происходит, потому что оператор

X NOT IN (A1, A2, AN)

эквивалентен выражению

X <> A1 AND X <> A2 AND …X <> AN

Если одно из Ai будет NULL, то результат этого выражения будет иметь значение НЕ ОПРЕДЕЛЕНО (UNKNOWN).

Пример 2.27. Вывести данные о сотрудниках, которые не работают в отделах с определенными номерами

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,

DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID NOT IN (30,50,60,80,90,100,NULL);

Результат выполнения этого запроса не будет содержать строк.

Оператор IS NULL

Оператор IS NULLиспользуется для определения строк с неопределенным значением заданного столбца.

Синтаксис:

{имя столбца} IS NULL

Данное выражение принимает значение TRUE, если значение проверяемого столба будет NULL.

Пример 2.28. Получить данные о сотрудниках, для которых неизвестен номер руководителя

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE manager_id IS NULL;

Пример 2.29. Вывести данные о сотрудниках, у которых не задан номер отдела

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE department_id IS NULL;

Можно также использовать разновидность данного оператора IS NOT NULL, который возвращает значение FALSE, если значение проверяемого столба будет NULL.