На что стоит обратить внимание, применяя оператор between в SQL?
Существует большое количество правил написания запросов SQL и они, несомненно, приводят к заметным и качественным результатам обработки данных. Но, иногда, даже при написании простых запросов, ускользают некоторые нюансы. О них и пойдет речь в данной статье.
В ходе работы с одной из БД, содержащих данные о транспортных средствах, возникла необходимость выгрузить модели автотранспорта, названия которых начинаются в диапазоне от «А» до «D».
Написав запрос на SQL, с помощью оператора between,в результатах выгрузки не оказалось таких марок авто, как datsun и daewoo, которые точно были в базе данных.
Начав разбираться, стало понятно следующее — если Вам нужно применить оператор between, который выбирает при запросе все значения из заданного диапазона (включая границы), то с датами и числовыми значениями не возникает трудностей, а вот с текстовыми строками всё иначе т.к. значение верхней границы диапазона не будет включено в выборку, если оно не указано точно.
Итак, у нас есть таблица «table1» со столбцом «model_car» (тип данных varchar) из которой нам необходимо вывести ее часть, где названия марок автомобилей лежат в диапазоне от А до D.
Предикат 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.
Оператор BETWEEN (Transact-SQL)
Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
test_expression
Выражение для проверки на принадлежность диапазону в пределах от begin_expression до end_expression. Выражение test_expression должно иметь тот же тип данных, что и begin_expression и end_expression.
Логическое НЕ
Указывает, что результат предиката должен быть инвертирован.
begin_expression
Любое допустимое выражение expression. Выражение begin_expression должно иметь тот же тип данных, что и test_expression и end_expression.
end_expression
Любое допустимое выражение expression. Выражение end_expression должно иметь тот же тип данных, что и test_expression и begin_expression.
И
Служит заполнителем, который указывает на то, что значение test_expression должно находиться в диапазоне от begin_expression до end_expression.
Типы результата
Boolean
Значение результата
Оператор BETWEEN возвращает значение TRUE, если значение аргумента test_expression больше значения аргумента begin_expression или равно ему и меньше значения аргумента end_expression или равно ему.
Оператор NOT BETWEEN возвращает значение TRUE, если значение аргумента test_expression меньше значения аргумента begin_expression или больше значения аргумента end_expression.
Замечания
Примеры
А. Использование оператора BETWEEN
В приведенном ниже примере возвращаются сведения о ролях базы данных. Первый запрос возвращает все роли. Во втором примере с помощью предложения BETWEEN роли ограничиваются указанными значениями database_id .
SELECT principal_id, name FROM sys.database_principals WHERE type = 'R'; SELECT principal_id, name FROM sys.database_principals WHERE type = 'R' AND principal_id BETWEEN 16385 AND 16390; GO principal_id name ------------ ---- 0 public 16384 db_owner 16385 db_accessadmin 16386 db_securityadmin 16387 db_ddladmin 16389 db_backupoperator 16390 db_datareader 16391 db_datawriter 16392 db_denydatareader 16393 db_denydatawriter principal_id name ------------ ---- 16385 db_accessadmin 16386 db_securityadmin 16387 db_ddladmin 16389 db_backupoperator 16390 db_datareader B. Использование операторов > и < вместо BETWEEN
-- Uses AdventureWorks SELECT e.FirstName, e.LastName, ep.Rate FROM HumanResources.vEmployee e JOIN HumanResources.EmployeePayHistory ep ON e.BusinessEntityID = ep.BusinessEntityID WHERE ep.Rate > 27 AND ep.Rate < 30 ORDER BY ep.Rate; GO FirstName LastName Rate --------- ------------------- --------- Paula Barreto de Mattos 27.1394 Janaina Bueno 27.4038 Dan Bacon 27.4038 Ramesh Meyyappan 27.4038 Karen Berg 27.4038 David Bradley 28.7500 Hazem Abolrous 28.8462 Ovidiu Cracium 28.8462 Rob Walters 29.8462 C. Использование оператора NOT BETWEEN
В следующем примере выводятся все строки вне указанного диапазона от 27 до 30 .
-- Uses AdventureWorks SELECT e.FirstName, e.LastName, ep.Rate FROM HumanResources.vEmployee e JOIN HumanResources.EmployeePayHistory ep ON e.BusinessEntityID = ep.BusinessEntityID WHERE ep.Rate NOT BETWEEN 27 AND 30 ORDER BY ep.Rate; GO D. Использование оператора BETWEEN со значениями типа datetime
В приведенном ниже примере возвращаются строки, в которых значения типа datetime находятся между '20011212' и '20020105' включительно.
-- Uses AdventureWorks SELECT BusinessEntityID, RateChangeDate FROM HumanResources.EmployeePayHistory WHERE RateChangeDate BETWEEN '20011212' AND '20020105'; BusinessEntityID RateChangeDate ----------- ----------------------- 3 2001-12-12 00:00:00.000 4 2002-01-05 00:00:00.000 Запрос извлекает ожидаемые строки, так как значения даты в запросе и значения типа datetime, хранящиеся в столбце RateChangeDate , были заданы без указания времени. Если время не указано, по умолчанию оно принимается равным 0:00. Обратите внимание, что строка, время в которой позднее 0:00 05.01.2002, не будет возвращена данным запросом, так как она не попадает в диапазон.
BETWEEN в T-SQL – примеры использования логического оператора
В этой статье мы рассмотрим логический оператор BETWEEN языка T-SQL, Вы узнаете, что это за оператор и как его использовать. А также мы, конечно же, разберём примеры SQL запросов с применением оператора BETWEEN.
Оператор BETWEEN в языке T-SQL
BETWEEN – это логический оператор языка T-SQL, который определяет диапазон для проверки. Другими словами, с помощью BETWEEN мы можем проверить, входит ли значение в определённый диапазон.
Синтаксис
test_expression [ NOT ] BETWEEN begin_expression AND end_expression
Описание аргументов
- test_expression – выражение, которое подлежит проверке на принадлежность к диапазону в пределах от begin_expression до end_expression;
- begin_expression – выражение, характеризующее начала диапазона;
- end_expression – выражение, характеризующее конец диапазона.
Все выражения должны иметь одинаковый тип данных.
В качестве результата оператор BETWEEN возвращает значения (Boolean):
- TRUE – если значение аргумента test_expression больше или равно значению begin_expression, при этом меньше или равно значению end_expression, т.е. test_expression входит в диапазон от begin_expression до end_expression;
- FALSE – если значение аргумента test_expression не входит в диапазон от begin_expression до end_expression.
С помощью ключевого слова NOT мы можем инвертировать результат предиката, иными словами, NOT BETWEEN означает, что мы хотим проверить значение на предмет того, что оно не входит в заданный диапазон.
В данном случае оператор NOT BETWEEN вернет TRUE, если значение аргумента test_expression не входит в диапазон от begin_expression до end_expression.
В случае если в BETWEEN хотя бы одно из выражений будет содержать значение NULL, результат будет не определён.
Заметка! Напоминаю, что необходимо всегда помнить о том, что существуют такие значения как NULL (это не пусто и не 0, это отсутствие значения), которые обрабатывается SQL сервером по-особому, результат SQL запроса с участием NULL-значений может быть неочевиден и непредсказуем.
Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.
Исходные данные для примеров
В качестве сервера у меня выступает Microsoft SQL Server 2016 Express. Для примера давайте представим, что у нас есть таблица TestTable, и в ней содержатся следующие данные (перечень товаров с указанием цены). Строки в таблицу я добавляю с помощью инструкции INSERT INTO.
--Создание таблицы CREATE TABLE TestTable( [ProductId] [INT] IDENTITY(1,1) NOT NULL, [ProductName] [VARCHAR](100) NOT NULL, [Price] [Money] NULL ) GO --Добавление строк в таблицу INSERT INTO TestTable(ProductName, Price) VALUES ('Системный блок', 300), ('Монитор', 200), ('Клавиатура', 100), ('Мышь', 50), ('Принтер', 200), ('Сканер', 150), ('Телефон', 250), ('Планшет', 300) GO --Выборка данных SELECT * FROM TestTable
Пример использования оператора BETWEEN в секции WHERE
Давайте напишем SQL запрос, который выведет нам все товары, цена которых находится в диапазоне от 100 до 200 рублей включительно.
--Условие с применением оператора BETWEEN SELECT ProductID, ProductName, Price FROM TestTable WHERE Price BETWEEN 100 AND 200
Данный запрос мы могли бы написать и без использования оператора BETWEEN, например, следующий SQL запрос абсолютно эквивалентен.
--Условие с применением операторов сравнения SELECT ProductID, ProductName, Price FROM TestTable WHERE Price >= 100 AND Price
Как видим, результат одинаковый, но в случае с BETWEEN условие выглядит более наглядно и понятно, к тому же выражение для проверки (Price) мы записали всего один раз, в случае с операторами сравнения два.
Пример использования оператора BETWEEN в условной конструкции IF
Оператор BETWEEN можно использовать не только в секции WHERE, но и в других конструкциях языка T-SQL, например, в условной конструкции IF. В следующем примере мы проверим переменную @TestVar на предмет того, входит ли значение этой переменной в диапазон от 1 до 10 и, если входит, выполним нужное нам действия, я для примера просто пошлю запрос SELECT.
DECLARE @TestVar INT = 5 IF @TestVar BETWEEN 1 AND 10 SELECT 'Переменная @TestVar находится в диапазоне от 1 до 10' AS [Результат]
Пример использования оператора NOT BETWEEN
Сейчас давайте напишем запрос с применением ключевого слова NOT, например, нам нужно вывести все товары, цена которых не входит в диапазон от 100 до 200 рублей (эквивалентный пример с использованием операторов сравнения я также прилагаю).
--Условие с применением оператора NOT BETWEEN SELECT ProductID, ProductName, Price FROM TestTable WHERE Price NOT BETWEEN 100 AND 200 --Условие с применением операторов сравнения SELECT ProductID, ProductName, Price FROM TestTable WHERE Price < 100 OR Price >200
В данном случае нам вывелись все товары, цена которых меньше 100 или больше 200 рублей.
В языке T-SQL кроме оператора BETWEEN существуют и другие логические операторы, например, оператор EXISTS, который в некоторых случаях бывает очень полезен, его мы также рассматривали на этом сайте.
У меня на этом все, пока!
