Предикат SQL BETWEEN — поиск значений из указанного интервала
С помощью предиката SQL BETWEEN можно извлечь из таблицы строки, в которых значения некоторого проверяемого столбца находятся в интервале, границы которого обозначены некоторым выражением. Границы интервала также включены в него.
Запросы с предикатом SQL BETWEEN имеют следующий синтаксис:
SELECT ИМЕНА_СТОЛБЦОВ FROM ИМЯ ТАБЛИЦЫ WHERE ИМЯ_СТОЛБЦА BETWEEN ВЫРАЖЕНИЕ_1 AND ВЫРАЖЕНИЕ_2
Если перед предикатом BETWEEN поставить ключевое слово NOT, то в выборку попадут строки, в которых значение проверяемого столбца находится за пределами интервала: до ВЫРАЖЕНИЯ_1 и после ВЫРАЖЕНИЯ_2.
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .
В первых примерах работаем с базой данных «Компания 2», содержащей данные о заработной плате сотрудников. Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными — в файле по этой ссылке .
Запросы с BETWEEN: интервал задан указанными числами
В самых простых запросах с предикатом BETWEEN границы интервала для проверяемого столбца заданы непосредственным указанием числовых значений.
Пример 1. Итак, работаем с базой данных фирмы и её таблицей Staff, содержащей данные о заработной плате сотрудников.
| ID | Name | Salary |
| 1 | Sanders | 18357.5 |
| 2 | Pernal | 15430.0 |
| 3 | Marenghi | 17506.8 |
| 4 | Doctor | 12322.4 |
| 5 | Factor | 16228.7 |
| 6 | Junkers | 16232.8 |
| 7 | Moonlight | 21500.6 |
| 8 | Aisen | 19540.7 |
| 9 | MacGregor | 15790.8 |
Требуется вывести сотрудников, заработная плата которых находится в интервале от 16 000 до 18 000. Пишем следующий запрос с предикатом BETWEEN:
SELECT * FROM Staff WHERE Salary BETWEEN 16000 AND 18000
Запрос выведет следующую результирующую таблицу:
| ID | Name | Salary |
| 3 | Marenghi | 17506.8 |
| 5 | Factor | 16228.7 |
| 6 | Junkers | 16232.8 |
Пример 2. База данных и таблица — те же, что в примере 1. Требуется вывести сотрудников, заработная плата которых находится вне интервала от 16 000 до 18 000. Пишем следующий запрос с предикатом BETWEEN, перед которым ставим ключевое слово NOT:
SELECT * FROM Staff WHERE Salary NOT BETWEEN 16000 AND 18000
Запрос выведет следующую результирующую таблицу:
| ID | Name | Salary |
| 1 | Sanders | 18357.5 |
| 2 | Pernal | 15430.0 |
| 4 | Doctor | 12322.4 |
| 7 | Moonlight | 21500.6 |
| 8 | Aisen | 19540.7 |
| 9 | MacGregor | 15790.8 |
Запросы с BETWEEN: интервал задан вложенными запросами
В запросах с BETWEEN границы интервала для проверяемого столбца можно не указывать явно, задав его некоторыми условиями, которые определены вложенными запросами.
Пример 3. Продолжаем работать с базой данных Staff. Требуется вывести сотрудников, заработная плата которых находится в интервале между средней и максимальной. Пишем следующий запрос, в котором границы заданы вложенными запросами:
SELECT * FROM Staff WHERE Salary BETWEEN ( SELECT AVG (Salary) FROM Staff ) AND ( SELECT MAX (Salary) FROM Staff )
Результатом выполнения запроса будет следующая таблица:
| ID | Name | Salary |
| 1 | Sanders | 18357.5 |
| 3 | Marenghi | 17506.8 |
| 7 | Moonlight | 21500.6 |
| 8 | Aisen | 19540.7 |
Пример 4. База данных и таблица — те же. Требуется вывести сотрудников, заработная плата которых находится в интервале между заработной платой сотрудника MacGregor и заработной платой сотрудика Marenghi. Пишем следующий запрос, в котором границы заданы вложенными запросами:
SELECT * FROM Staff WHERE Salary BETWEEN ( SELECT Salary FROM Staff WHERE Name=’MacGregor’) AND ( SELECT Salary FROM Staff WHERE Name=’Marenghi’)
Результатом выполнения запроса будет следующая таблица:
| ID | Name | Salary |
| 3 | Marenghi | 17506.8 |
| 5 | Factor | 16228.7 |
| 6 | Junkers | 16232.8 |
| 9 | MacGregor | 15790.8 |
Как видим, граничные значения интервала попали в результирующую таблицу.
Написать запрос с предикатом BETWEEN самостоятельно, а затем посмотреть решение
Пример 5. Продолжаем работать с таблицей Staff. Требуется вывести сотрудников с ID между ID сотрудника Marenghi и ID сотрудника Moonlight.
Запросы с BETWEEN: интервал задан значениями даты и времени
Интервал для извлечения строк с помощью предиката BETWEEN может быть задан не только значениями числового типа данных, но и значениями даты и времени.
Далее работаем с базой данных «Недвижимость». Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными — в файле по этой ссылке .
Пример 6. Итак, работаем с базой данных «Недвижимость».
Таблица Deal содержит данные о сделках. Нам потребуется столбец Date, в котором указывается дата совершения сделки. Для упрощения будем считать, что в таблице представлены сделки, заключённые в течение одного года. Требуется вывести сделки, заключённые в августе и сентябре. Пишем следующий запрос:
SELECT * FROM Deal WHERE MONTH(Date) BETWEEN 08 AND 09
Примеры запросов к базе данных «Недвижимость» есть также в уроках по оператору GROUP BY, предикату EXISTS, функциям ALL и ANY и LIMIT.
SQL условие BETWEEN
SQL условие BETWEEN позволяет легко проверить, находится ли выражение в диапазоне значений (включительно). Его можно использовать в операторе SELECT, INSERT, UPDATE или DELETE.
Синтаксис
Синтаксис для условия BETWEEN в SQL:
expression BETWEEN value1 AND value2;
Параметры или аргументы
expression Столбец или расчет. value1 и value2 Эти значения создают инклюзивный диапазон, с которым сравнивается expression .
Примечание
Условие SQL BETWEEN будет возвращать записи, где выражение находится в диапазоне значений value1 и value2 (включительно).
Пример — использование условия BETWEEN с числовыми значениями
Давайте рассмотрим пример использования условия BETWEEN для получения значений в числовом диапазоне.
В этом примере у нас есть таблица suppliers со следующими данными:
| supplier_id | supplier_name | city | state |
|---|---|---|---|
| 100 | Yandex | Moscow | Moscow |
| 200 | Lansing | Michigan | |
| 300 | Oracle | Redwood City | California |
| 400 | Bing | Redmond | Washington |
| 500 | Yahoo | Sunnyvale | Washington |
| 600 | DuckDuckGo | Paoli | Pennsylvania |
| 700 | Qwant | Paris | Ile de France |
| 800 | Menlo Park | California | |
| 900 | Electronic Arts | San Francisco | California |
Выполните следующий оператор SELECT:
Transact-SQL
FROM suppliers
WHERE supplier_id BETWEEN 300 AND 600 ;
Будет выбрано 4 записи. Вот результаты, которые вы должны получить:
| supplier_id | supplier_name | city | state |
|---|---|---|---|
| 300 | Oracle | Redwood City | California |
| 400 | Bing | Redmond | Washington |
| 500 | Yahoo | Sunnyvale | Washington |
| 600 | DuckDuckGo | Paoli | Pennsylvania |
В этом примере возвращаются все строки из таблицы suppliers , где supplier_id находится в диапазоне от 300 до 600 (включительно). Это эквивалентно следующему запросу SELECT:
FROM suppliers
WHERE supplier_id > = 300
AND supplier_id < = 600;
Пример — использование условия BETWEEN со значениями даты
Даты могут быть несколько сложными в SQL, и то, как вы используете условие BETWEEN с датами, зависит от базы данных, которую вы используете (т.е. Oracle, SQL Server, MySQL и т.д.). Мы покажем вам пример для каждой из основных технологий баз данных. Итак, начнем.
В этом примере у нас есть таблица orders и следующими данными:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2019/06/18 |
| 2 | 5000 | 2019/06/18 |
| 3 | 8000 | 2019/06/19 |
| 4 | 4000 | 2019/06/20 |
| 5 | NULL | 2019/07/01 |
Введите один из следующих операторов SQL, в зависимости от базы данных, которую вы используете.
Для SQL Server, PostgreSQL и SQLite:
FROM orders
WHERE order_date BETWEEN ‘2019/06/19’ AND ‘2019/07/01’ ;
Для Oracle (используйте функцию TO_DATE):
Transact-SQL
FROM orders
WHERE order_date BETWEEN TO_DATE ( ‘19.06.2019’ , ‘dd.mm.yyyy’ )
AND TO_DATE ( ‘01.07.2019’ , ‘dd.mm.yyyy’ );
Для MySQL и MariaDB (используйте функцию CAST):
FROM orders
WHERE order_date BETWEEN CAST ( ‘2019/06/19’ AS DATE ) AND CAST ( ‘2019/07/01’ AS DATE );
Будет выбрано 3 записи. Вот результаты, которые вы получите:
| order_id | customer_id | order_date |
|---|---|---|
| 3 | 8000 | 2019/06/19 |
| 4 | 4000 | 2019/06/20 |
| 5 | NULL | 2019/07/01 |
В этом примере будут возвращены все записи из таблицы orders , где значение order_date находится между 19 июня 2019 г. и 1 июля 2019 г. (включительно).
Пример — использование оператора NOT с условием BETWEEN
Условие BETWEEN может использоваться с оператором NOT для создания условия NOT BETWEEN. Давайте рассмотрим пример, который показывает, как использовать условие NOT BETWEEN в запросе.
В этом примере у нас есть таблица customer, содержащая следующие данные:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
Введите следующий SQL оператор:
Оператор SQL BETWEEN
Оператор SQL BETWEEN задает диапазон, в котором будет осуществляться проверка условия.
Оператор SQL BETWEEN имеет следующий синтаксис:
test_expression [NOT] BETWEEN begin_expression AND end_expression
- test_expression — задает объект для проверки по диапазону;
- start_expression — начальное значение диапазона;
- end_expression — конечное значение диапазона;
Примеры оператора SQL BETWEEN: Имеется следующая таблица Universities :
| ID | UniversityName | Students | Faculties | Professores | Location | Site |
| 1 | Perm State National Research University | 12400 | 12 | 1229 | Perm | psu.ru |
| 2 | Saint Petersburg State University | 21300 | 24 | 13126 | Saint-Petersburg | spbu.ru |
| 3 | Novosibirsk State University | 7200 | 13 | 1527 | Novosibirsk | nsu.ru |
| 4 | Moscow State University | 35100 | 39 | 14358 | Moscow | msu.ru |
| 5 | Higher School of Economics | 20335 | 12 | 1615 | Moscow | hse.ru |
| 6 | Ural Federal University | 57000 | 19 | 5640 | Yekaterinburg | urfu.ru |
| 7 | National Research Nuclear University | 8600 | 10 | 936 | Moscow | mephi.ru |
Пример 1. Используя оператор SQL BETWEEN вывести записи тех университетов, число студентов (Students) которых от 10000 до 30000:
SELECT * FROM Universities WHERE Students BETWEEN 10000 AND 30000
| ID | UniversityName | Students | Faculties | Professores | Location | Site |
| 1 | Perm State National Research University | 12400 | 12 | 1229 | Perm | psu.ru |
| 2 | Saint Petersburg State University | 21300 | 24 | 13126 | Saint-Petersburg | spbu.ru |
| 5 | Higher School of Economics | 20335 | 12 | 1615 | Moscow | hse.ru |
Данный пример можно так же записать и с помощью операторов сравнения (,=) и оператора SQL WHERE. Так, нижеприведенный код будет выведет точно такой же результат:
SELECT * FROM Universities WHERE Students > 10000 AND Students < 30000
Пример 2. Используя оператор SQL BETWEEN вывести записи университетов, число преподавателей (Professores) которых менее 2000 и более 14000:
SELECT * FROM Universities WHERE Professores NOT BETWEEN 2000 AND 14000
| ID | UniversityName | Students | Faculties | Professores | Location | Site |
| 1 | Perm State National Research University | 12400 | 12 | 1229 | Perm | psu.ru |
| 3 | Novosibirsk State University | 7200 | 13 | 1527 | Novosibirsk | nsu.ru |
| 4 | Moscow State University | 35100 | 39 | 14358 | Moscow | msu.ru |
| 5 | Higher School of Economics | 20335 | 12 | 1615 | Moscow | hse.ru |
| 7 | National Research Nuclear University | 8600 | 10 | 936 | Moscow | mephi.ru |
Опeратор Between в Oracle SQL: включает границы или нет. Примеры использования
Да, оператор BETWEEN в SQL включает границы диапазона. Под границами подразумевают верхнее и нижнее значения диапазона, которые входят в исчисление. Например , вы хотите извлечь из таблицы идентификаторы автомобилей (autoname_id) с определенным объемом продаж (от 100000 до 200000 штук за год). Объемы продаж располагаются в столбце «volum_sales». В этом случае синтаксис инструкции будет такой:
SELECT autoname_id FROM autonames WHERE volum_sales BETWEEN 100000 AND 200000
Главное , что нужно знать о границах в диапазоне в этом случае, что значения «100000» и «200000» определяют диапазон и они включены в выборку. То есть автомобили с объемом 200000 штук в год попадут в выборку, а 200001 уже нет. То же самое с нижней границей: автомобили с объемом 100000 штук в год попадут в выборку, а 99999 уже нет.
Оператор BETWEEN в SQL: примеры
- Когда взаимодействие оператора происходит с числами, тогда верхняя и нижняя границы входят в выборку.
- Нижнее значение диапазона обязательно должно быть меньше чем верхнее значение. Если это условие не соблюдается, тогда диапазон значений не будет выделен, а значит команда с оператором BETWEEN не сработает. Важно помнить об этой особенности, когда в качестве аргументов диапазона оператора BETWEEN используются не конкретные значения, а переменные.
- Когда взаимодействие оператора BETWEEN происходит с текстом, тогда значения границ диапазона не входят в выборку.
Заключение
Оператор BETWEEN активно применяется в SQL. Главное , что о нем нужно запомнить — он создает диапазон значений, из которого мы можем извлекать какую-то информацию. Если нужно, тогда мы можем извлекать информацию вне диапазона значений, тогда необходимо задействовать дополнительный SQL-оператор — "NOT".
Синтаксис написания запросов с оператором BETWEEN в разных СУДБ отличается. Поэтому перед применением этого оператора почитайте инструкцию его применения в вашей СУБД, чтобы не возникало дополнительных проблем.
Мы будем очень благодарны
если под понравившемся материалом Вы нажмёте одну из кнопок социальных сетей и поделитесь с друзьями.