При работе с реляционными СУБД, в которых данные хранятся в табличном виде, пользователи часто сталкиваются с задачей выборки значений, входящих (не входящих) в определенный диапазон. Язык SQL позволяет задать множество, которому должно (не должно) принадлежать значение различными вариантами - оператором In, оператором Like, комбинацией условий больше - меньше, а также оператором SQL Between. Описание и примеры в данной статье будут посвящены последнему варианту.
Оператор «Между» в SQL: синтаксис, ограничения
Дословно оператор between SQL так и переводится - «между». Его использование позволяет задать ограничение «От и До» к конкретному полю, и если очередное значение попадет в диапазон, то предикат примет значение «Истина», и значение попадет в итоговую выборку.
Синтаксис у оператора предельно простой:
Where t1.n between 0 and 7 |
Как видим, после ключевого слова between необходимо указать значение нижней границы диапазона, затем AND и значение верхней границы.
Перечислим, с какими типами данных может работать оператор between SQL:
- С числами - целыми и дробными.
- С датами.
- С текстом.
У данного оператора between SQL есть определенные особенности. Познакомимся с ними:
- При работе с числами и датами значения ограничений «От и До» включаются в выборку.
- Значение нижней границы диапазона должно быть меньше значения верхней границы, иначе не будет выведено ничего, ведь условие логически не верно. Особенно внимательным нужно быть, когда вместо конкретных значений в условие включаются переменные.
При работе с текстом значение верхней границы диапазона не будет включено в выборку, если оно не указано предельно точно. В следующих разделах рассмотрим данную особенность подробнее.
Выборка чисел и дат в определенном диапазоне
Подготовим таблицу с данными по менеджерам, работающим в организации. Таблица будет иметь следующую структуру:
Имя поля | Тип данных | Описание |
Уникальный идентификатор сотрудника |
||
Текстовый | Фамилия сотрудника |
|
Текстовый | Имя сотрудника |
|
Отчество | Текстовый | Отчество сотрудника |
Текстовый | Пол сотрудника (М/Ж) |
|
Дата_приема | Дата/время | Дата приема сотрудника на работу |
Число_детей | Числовой | Количество детей у сотрудника |
Заполним таблицу следующими данными:
Код | Фамилия | Имя | Отчество | Пол | Дата_приема | Число_детей |
Александрова | Николаевна | |||||
Степанович | ||||||
Виноградов | Павлович | |||||
Александр | Борисович | |||||
Вишняков | Александрович | |||||
Тропников | Сергеевич | |||||
Жемчугов | Васильевич | |||||
Константиновна | ||||||
Николаевич |
Составим between, который поможет нам выбрать всех сотрудников, имеющих 2 или 3 ребенка:
Результатом станет три строки с данными по сотрудникам с фамилиями Шумилин, Тропников и Авдеева.
Теперь выберем сотрудников, принятых с 1 января 2005 года по 31 декабря 2016 года. Следует отметить, что разные СУБД по-разному позволяют записывать в условия даты. В большинстве случаев дату просто принудительно приводят к виду день-месяц-год (или как удобнее) и записывают в одинарные или В СУБД дату заключают в знак «#». Выполним пример как раз на ее основе:
SELECT Менеджеры.*, Менеджеры.Дата_приема FROM Менеджеры WHERE Менеджеры. Дата_приема Between #1/1/2005# And #31/12/2016# |
Результатом станут пять сотрудников, принятых на работу в указанный период включительно.
Работа в between со строками
Очень частая задача, которую приходится решать при работе с фамилиями сотрудников, - это необходимость выбрать только тех, чьи фамилии начинаются на определенную букву. Попробуем и мы выполнить запрос и выбрать сотрудников, чьи фамилии начинаются на фамилии с А до В:
Результат следующий:
Как видим, двое сотрудников, имеющих фамилию на букву В, в список не попали. С чем это связано? Дело в том, каким именно образом оператор сравнивает строки неравной длины. Строка «В» короче строки «Виноградов» и дополняется пробелами. Но при сортировке по алфавиту пробелы окажутся опережающими символами, и фамилия в выборку не попадет. Разные СУБД по-разному предлагают решать данную проблему, но зачастую проще всего для надежности указывать следующую букву алфавита в диапазоне:
При выполнении данного запроса результат нас полностью удовлетворит.
Такой нюанс существует только при работе с символьными данными, однако он показывает, что при работе даже с такими простыми операторами, как between, надо быть внимательными.
Любой запрос, создаваемый для работ в БД, упрощает допуск к нужной информации. В предыдущей записи я говорил об общих операторах условий. В этой же записи я поговорю об операторах, которые позволят создавать запросы, способные выдать более подробную интересующую информацию, которую в то же, запросами с операторами AND, OR не так просто найти.
Одним из специальных операторов является IN
. Данный оператор позволяет задавать необходимый диапазон отображения нужной информации. Вернёмся к данным по дожникам
Debtors
Num | Month | Year | Sname | City | Address | Debt |
0001 | Июль | 2012 | Иванов | Ставрополь | Ставропольская, 1 | 50000 |
0002 | Декабрь | 2019 | Кононов | Татарка | Загородная, 254 | 684068 |
0003 | Май | 2013 | Ямшин | Михайловск | Сельская, 48 | 165840 |
0004 | Август | 2012 | Прени | Ставрополь | Центральная, 16 | 46580 |
... | ... | ... | ... | ... | ... | ... |
9564 | Март | 2015 | Улиева | Дёмино | Международная, 156 | 435089 |
9565 | Октябрь | 2012 | Павлова | Ставрополь | Вокзальная, 37 | 68059 |
9566 | Январь | 2012 | Урюпа | Михайловск | Фонтанная, 19 | 51238 |
9567 | Ноябрь | 2017 | Вальетов | Татарка | Выездная, 65 | 789654 |
Предположим, необходимо выбрать всех должников города Ставрополь или Татарка. По аналогии с предыдущей записью, нужно было бы использовать запрос
SELECT
*
FROM
Debtors
WHERE
City = "Ставрополь"
OR
City = "Татарка";
Прежде всего получается громоздкий код. С использованием специальных операторов, можно получить более компактный код.
SELECT
*
FROM
Debtors
WHERE
City IN
("Ставрополь", "Татарка");
Результатом будет
Проследим логику программы. С ключевыми словами SELECT, FROM и WHERE. А вот дальше появляется оператор IN. Он задаёт программе последовательность действий - необходимо просмотреть информацию БД, содержащую в столбце "City". А для отображения нужно выбрать данные "Ставрополь" и "Татарка".
Рассмотрю пример, в котором нужно сделать отбор по определённым суммам долга.
SELECT
*
FROM
Debtors
WHERE
Debt IN
(435089, 789654, 684068);
Результатом будет следующее
Т.е. оператор IN просматривает всю БД на наличие указанных параметров отбора информации.
Иначе обстоит дело с использованием другого специального оператора BETWEEN
. Если оператор IN
рассматривал информацию с исключительно указанными параметрами, то оператор BETWEEN
- между определёнными диапазонами. Однако, не следует проводить аналогию между переводом с английского данного оператора и его действительным предназначением. Если указывать BETWEEN 1 AND 5, то это не означает, что истинной будут числа 2, 3 и 4. Данный оператор просто воспринимается SQL как некое значение, которое может находится среди других значений. На примере это будет выглядеть следующим образом.
SELECT
*
FROM
Debtors
WHERE
Debts BETWEEN
30000 AND
100000;
Результатом будет являться
То есть SQL воспринял оператор BETWEEN
как любое значение, находящееся в диапазоне от 30000 до 100000 по столбцу "Debts".
Кроме задания приблизительных диапазонов в цифровом выражении, можно задавать алфавитные диапазоны, в которых отображается информация, содержащая первые буквы из указанного диапазона. Но, тут есть один интересный момент. Создадим следующий запрос
SELECT
*
FROM
Debtors
WHERE
Sname BETWEEN
"И" AND
"П";
Тогда отобразятся следующие данные
Закономерный вопрос: "А почему из списка выпали должники, с фамилией П рени и П авлова? Ведь первые буквы их фамилий входят в указанный диапазон!" Буквы входят, а фамилии - нет. Это связано с тем, что язык 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-значений может быть неочевиден и непредсказуем.
Заметка! Если Вы начинающий программист и хотите изучить язык T-SQL с нуля, то рекомендую почитать мою книгу «Путь программиста T-SQL », в ней я подробно рассказываю про основы языка T-SQL, а также уделяю немало внимания и продвинутым конструкциям.
Исходные данные для примеров
В качестве сервера у меня выступает Microsoft SQL Server 2016 Express . Для примера давайте представим, что у нас есть таблица TestTable, и в ней содержатся следующие данные (перечень товаров с указанием цены ). Строки в таблицу я добавляю с помощью инструкции INSERT INTO .
Создание таблицы CREATE TABLE TestTable( IDENTITY(1,1) NOT NULL, (100) NOT NULL, 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 <= 200
Как видим, результат одинаковый, но в случае с 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 , который в некоторых случаях бывает очень полезен, его мы также рассматривали на этом сайте.
У меня на этом все, пока!