BETWEEN в T-SQL – примеры использования логического оператора. Операторы IN и BETWEEN в SQL Between sql включает или нет
При работе с реляционными СУБД, в которых данные хранятся в табличном виде, пользователи часто сталкиваются с задачей выборки значений, входящих (не входящих) в определенный диапазон. Язык 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, надо быть внимательными.
Оператор 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 в том виде, как это описано выше.
Любой запрос, создаваемый для работ в БД, упрощает допуск к нужной информации. В предыдущей записи я говорил об общих операторах условий. В этой же записи я поговорю об операторах, которые позволят создавать запросы, способные выдать более подробную интересующую информацию, которую в то же, запросами с операторами 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 в подобного рода запросах, воспринимает только ту длину поисковых строк, которые заданы. Другими словами, длина строки "П" в запросе составляет один символ, а длина строки "Прени" и "Павлова" в базе данных - пять и семь соответственно. А вот фамилия "И ванов" попадает в диапазон, поскольку диапазон начинается с И , как начала, длиной от одного символа.
Определяет, попадает ли значение выражения в указанный интервал. Данный оператор можно использовать в инструкциях 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, если в одних индексах есть расширения, а в других нет).