Главная > Стиральные машины > Between SQL: примеры, описание. BETWEEN в T-SQL – примеры использования логического оператора Пример использования оператора BETWEEN в секции WHERE


Between SQL: примеры, описание. BETWEEN в T-SQL – примеры использования логического оператора Пример использования оператора BETWEEN в секции WHERE




При работе с реляционными СУБД, в которых данные хранятся в табличном виде, пользователи часто сталкиваются с задачей выборки значений, входящих (не входящих) в определенный диапазон. Язык SQL позволяет задать множество, которому должно (не должно) принадлежать значение различными вариантами - оператором In, оператором Like, комбинацией условий больше - меньше, а также оператором SQL Between. Описание и примеры в данной статье будут посвящены последнему варианту.

Оператор «Между» в SQL: синтаксис, ограничения

Дословно оператор between SQL так и переводится - «между». Его использование позволяет задать ограничение «От и До» к конкретному полю, и если очередное значение попадет в диапазон, то предикат примет значение «Истина», и значение попадет в итоговую выборку.

Синтаксис у оператора предельно простой:

Where t1.n between 0 and 7

Как видим, после ключевого слова between необходимо указать значение нижней границы диапазона, затем AND и значение верхней границы.

Перечислим, с какими типами данных может работать оператор between SQL:

  1. С числами - целыми и дробными.
  2. С датами.
  3. С текстом.

У данного оператора between SQL есть определенные особенности. Познакомимся с ними:

  1. При работе с числами и датами значения ограничений «От и До» включаются в выборку.
  2. Значение нижней границы диапазона должно быть меньше значения верхней границы, иначе не будет выведено ничего, ведь условие логически не верно. Особенно внимательным нужно быть, когда вместо конкретных значений в условие включаются переменные.

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

Выборка чисел и дат в определенном диапазоне

Подготовим таблицу с данными по менеджерам, работающим в организации. Таблица будет иметь следующую структуру:

Имя поля

Тип данных

Описание

Уникальный идентификатор сотрудника

Текстовый

Фамилия сотрудника

Текстовый

Имя сотрудника

Отчество

Текстовый

Отчество сотрудника

Текстовый

Пол сотрудника (М/Ж)

Дата_приема

Дата/время

Дата приема сотрудника на работу

Число_детей

Числовой

Количество детей у сотрудника

Заполним таблицу следующими данными:

Код

Фамилия

Имя

Отчество

Пол

Дата_приема

Число_детей

Александрова

Николаевна

Степанович

Виноградов

Павлович

Александр

Борисович

Вишняков

Александрович

Тропников

Сергеевич

Жемчугов

Васильевич

Константиновна

Николаевич

Составим between, который поможет нам выбрать всех сотрудников, имеющих 2 или 3 ребенка:

Результатом станет три строки с данными по сотрудникам с фамилиями Шумилин, Тропников и Авдеева.

Теперь выберем сотрудников, принятых с 1 января 2005 года по 31 декабря 2016 года. Следует отметить, что разные СУБД по-разному позволяют записывать в условия даты. В большинстве случаев дату просто принудительно приводят к виду день-месяц-год (или как удобнее) и записывают в одинарные или В СУБД дату заключают в знак «#». Выполним пример как раз на ее основе:

SELECT Менеджеры.*, Менеджеры.Дата_приема

FROM Менеджеры

WHERE Менеджеры. Дата_приема Between #1/1/2005# And #31/12/2016#

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

Работа в between со строками

Очень частая задача, которую приходится решать при работе с фамилиями сотрудников, - это необходимость выбрать только тех, чьи фамилии начинаются на определенную букву. Попробуем и мы выполнить запрос и выбрать сотрудников, чьи фамилии начинаются на фамилии с А до В:

Результат следующий:

Как видим, двое сотрудников, имеющих фамилию на букву В, в список не попали. С чем это связано? Дело в том, каким именно образом оператор сравнивает строки неравной длины. Строка «В» короче строки «Виноградов» и дополняется пробелами. Но при сортировке по алфавиту пробелы окажутся опережающими символами, и фамилия в выборку не попадет. Разные СУБД по-разному предлагают решать данную проблему, но зачастую проще всего для надежности указывать следующую букву алфавита в диапазоне:

При выполнении данного запроса результат нас полностью удовлетворит.

Такой нюанс существует только при работе с символьными данными, однако он показывает, что при работе даже с такими простыми операторами, как between, надо быть внимательными.

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

Синтаксис

выражение [Not ] Between значение1 And значение2

Синтаксис оператора Between...And включает в себя следующие компоненты:

Замечания

Если значение компонента выражение находится между значением1 и значением2 (включительно), оператор Between...And возвращает значение True ; в противном случае возвращается значение False . Включение логического оператора Not приводит к проверке противоположного условия (предполагающего, что компонент выражение находится вне интервала, определенного компонентами значение1 и значение2 ).

С помощью Betwee n...And можно определить, попадает ли значение поля в указанный числовой диапазон. В примере ниже определяется, был ли заказ отправлен по адресу с почтовым индексом из заданного диапазона. Если почтовый индекс находится в диапазоне между 98101 и 98199, функция IIf возвращает Local (Местный). В противном случае она возвращает значение Nonlocal (Не местный).

SELECT IIf(PostalCode Between 98101 And 98199, “Local”, “Nonlocal”) FROM Publishers

Если выражение , значение1 или значение2 имеет значение Null, Between...And возвращает значение Null .

Поскольку подстановочные знаки, например звездочка (*), считаются литералами, их нельзя использовать в операторе Between...And . Например, нельзя использовать выражения вида 980* и 989*, чтобы найти все индексы, начинающиеся с чисел в интервале от 980 до 989. Существует два способа решения этой задачи. Можно добавить в запрос выражение, которое передает оператору Between...And первые три символа текстового поля. Другой вариант - добавить к нижней и верхней границам проверяемого интервала дополнительные цифры, в данном случае - от 98000 до 98999 или от 98000 до 98999-9999, если используются расширенные почтовые индексы (у нижних индексов необходимо опустить -0000, так как в противном случае будет пропущен индекс 98000, если в одних индексах есть расширения, а в других нет).

Любой запрос, создаваемый для работ в БД, упрощает допуск к нужной информации. В предыдущей записи я говорил об общих операторах условий. В этой же записи я поговорю об операторах, которые позволят создавать запросы, способные выдать более подробную интересующую информацию, которую в то же, запросами с операторами 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 выполняет булеву проверку значения на соответствие диапазону значений. Оператор возвращает TRUE, если значение входит в диапазон, и FALSE, если значение не входит в диапазон. Если любое значение диапазона равно NULL (неизвестно), то результат будет NULL.

Оператор EXISTS семантически эквивалентен оператору ANY/SOME.

Синтаксис SQL 2003

SELECT * WHERE выражение BETWEEN нижняя_граница AND верхняя_граница

Ключевые слова

WHERE выражение

Проверяет скалярное выражение (например, столбец) на соответствие диапазону значений, лежащих между верхней_границей и нижней_границей. BETWEEN нижняя _граница AND верхняя_граница

Сравнивает выражение с нижней_границей и верхней_границей. Сравнение включает крайние значения, то есть это все равно что «где выражение [не] больше или равно нижней_границе и меньше или равно верхней_границей>.

Общие правила

Оператор BETWEEN используется для проверки выражения на соответствие диапазону значений. Оператор BETWEEN может использоваться с любым типом данных, за исключением BLOB, CLOB, NCLOB, REF и ARRAY.

Например, нам нужно увидеть номера произведений (title_id), для которых объемы продаж с начала года (ytd_sales) лежат в диапазоне от 10000 до 20000.

SELECT title_id FROM titles WHERE ytcLsales BETWEEN 10000 AND 20000

Оператор BETWEEN включает границы диапазона. В данном случае в результат будут включены значения 10000 и 20000. Если вам нужно провести поиск, не включая границы диапазона, вы должны использовать символы «больше» (>) и меньше (<).

SELECT title_id FROM titles WHERE ytd.sales > 10000 AND ytd_sales < 20000

Оператор NOT позволяет проводить поиск за пределами диапазона, указанного в операторе BETWEEN. Так, вы можете найти номера всех произведений (title_id), которые публиковались не в 2003 году.

Некоторые программисты очень привередливо относятся к тому, как в предложениях WHERE используется ключевое слово AND. Чтобы незнакомый с кодом человек не подумал, что оператор AND, используемый в операторе BETWEEN, является логическим оператором, вы можете заключить все предложение BETWEEN в скобки.

SELECT title_id FROM titles WHERE (ytd_sales BETWEEN" 10000 AND 20000) AND pubdate >= "1991-06-12 00:00:00.000"

Различия в реализациях на разных платформах

Все платформы поддерживают оператор BETWEEN в том виде, как это описано выше.

В этой статье мы рассмотрим логический оператор 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 , который в некоторых случаях бывает очень полезен, его мы также рассматривали на этом сайте.

У меня на этом все, пока!