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

Задачу нахождения пробелов и диапазонов в SQL очень часто приходится решать в реальных жизненных ситуациях. Основной принцип заключается в том, что у вас есть определенная последовательность чисел или значений дат и времени, между которыми должен соблюдаться фиксированный интервал, но некоторые элементы отсутствуют. Решение задачи поиска пробелов подразумевает нахождение элементов, которых не хватает в последовательности, а поиск диапазонов - нахождение непрерывных диапазонов существующих значений. Для демонстрации методики поиска пробелов и диапазонов я воспользуюсь таблицей по имени T1 с численной последовательностью в столбце col1 с целым интервалом, равным единице, и таблицу T2 с последовательностью метода даты и времени в столбце col1 с интервалом в один день. Вот код создания T1 и T2 и наполнения их тестовыми данными:

SET NOCOUNT ON; USE TSQL2012; -- dbo.T1 (numeric sequence with unique values, interval: 1) IF OBJECT_ID("dbo.T1", "U") IS NOT NULL DROP TABLE dbo.T1; CREATE TABLE dbo.T1 (col1 INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY); GO INSERT INTO dbo.T1(col1) VALUES(2),(3),(7),(8),(9),(11),(15),(16),(17),(28); -- dbo.T2 (temporal sequence with unique values, interval: 1 day) IF OBJECT_ID("dbo.T2", "U") IS NOT NULL DROP TABLE dbo.T2; CREATE TABLE dbo.T2 (col1 DATE NOT NULL CONSTRAINT PK_T2 PRIMARY KEY); GO INSERT INTO dbo.T2(col1) VALUES ("20120202"), ("20120203"), ("20120207"), ("20120208"), ("20120209"), ("20120211"), ("20120215"), ("20120216"), ("20120217"), ("20120228");

Пробелы

Как говорилось ранее, задача поиска пробелов предусматривает нахождение диапазонов отсутствующих значений в последовательности. Для наших тестовых данных требуемый результат для численной последовательности в T1 таков:

А вот нужный результат для последовательности меток дат и времени в T2:

В версиях SQL Server предшествующих SQL Server 2012 методики работы с пробелами были довольно дорогими и подчас сложными. Но с появлением функций LAG и LEAD эту задачу стало возможным решать просто и эффективно. Давайте текущее значение в последовательности col1 назовем cur, следующее значение в последовательности назовем nxt. Затем можно фильтром отобрать только пары, разница между которыми больше интервала. Затем надо добавить один интервал к cur и отнять интервал от nxt, чтобы получить сведения о пробеле. Вот полное решение для числовой последовательности и план его выполнения:

Полюбуйтесь, насколько эффективен этот план: в нем выполняется только один упорядоченный просмотр индекса на основе столбца col1. Для применения этой же методики к временной последовательности надо просто задействовать функцию DATEDIFF для вычислении разницы между cur и nxt, а затем функцию DATEADD для добавления или вычитания интервала:

Диапазоны

Задача нахождения диапазонов подразумевает выявление диапазонов существующих значений. Вот ожидаемый результат для числовой последовательности:

А вот требуемый результат для временной последовательности дат:

Одно из самых эффективных решений задачи поиска диапазонов предусматривает использование ранжирования. Используется функция DENSE_RANK для создания последовательности целых чисел в упорядочении по col1 и вычисляется разница между col1 и «плотным рангом» (drnk), примерно так;

SELECT col1, DENSE_RANK() OVER(ORDER BY col1) AS drnk, col1 - DENSE_RANK() OVER(ORDER BY col1) AS diff FROM dbo.T1;

Заметьте, что в пределах диапазона разница одинакова, причем она уникальна для каждого диапазона. Это происходит потому, что col1 и drnk увеличиваются с одним интервалом. При переходе на следующий диапазон col1 увеличивается более, чем на один интервал, a drnk всегда увеличивается на один интервал. Поэтому разница в каждом последующем интервале больше, чем в предыдущем. Благодаря тому, что эта разница одинакова и уникальна в пределах каждого диапазона, можно использовать ее в качестве идентификатора группы. Так что остается только сгруппировать строки по этой разнице и вернуть максимальное и минимальное значение col1 в каждой группе:

WITH C AS (SELECT col1, col1 - DENSE_RANK() OVER(ORDER BY col1) AS grp FROM dbo.T1) SELECT MIN(col1) AS start_range, MAX(col1) AS end_range FROM C GROUP BY grp;

План этого решения показан на рисунке:

План очень эффективен, потому что при вычислении плотного ранга используется упорядочение индекса на основе col1.Возможно, вы поинтересуетесь, почему я использую функцию DENSE_RANK, а не ROW_NUMBER. Это нужно для тех случаев, когда не гарантируется уникальность значений последовательности. При использовании функции ROW_NUMBER эта методика работает, только если значения последовательности уникальны (таковы наши тестовые данные), и отказывает, если разрешены дубликаты. При использовании DENSE_RANK решение работает как для уникальных, так и для неуникальных значений, поэтому я всегда предпочитаю использовать функцию DENSE_RANK.

Эта же методика применима к временным интервалам, но решение не так очевидно. Вспомните, что в описанном решении создается идентификатор групп, а именно значение, одинаковое для всех членов одного диапазона и отличающееся от значений для членов в других диапазонах. Во временных последовательностях интервалы между значениями col1 и плотного ранга разные - у первого интервал день, а у второго единица. Чтобы этот способ работал, просто вычтите из значения col1 количество временных интервалов, равное плотному рангу. Для этого надо воспользоваться функцией DATEADD. Тогда вы получите метку даты и времени, которая одинакова для всех членов одного диапазона и отличается от значений в других диапазонах.

Вот код законченного решения:

WITH C AS (SELECT col1, DATEADD(day, -1 * DENSE_RANK() OVER(ORDER BY col1), col1) AS grp FROM dbo.T2) SELECT MIN(col1) AS start_range, MAX(col1) AS end_range FROM C GROUP BY grp;

Как видите, вместо прямого вычитания результата функции плотного ранга из col1, мы применяем DATEADD для вычитания из col1 плотного ранга, умноженного на интервал, то есть день.

Есть много задач, в которых требуется применять методику расчета диапазонов, в том числе отчеты о доступности, периодах активности и другие. Эту же методику можно применять для решения классической задачи упаковки интервалов дат. Допустим, есть такая таблица с информацией об интервалах дат:

IF OBJECT_ID("dbo.Intervals", "U") IS NOT NULL DROP TABLE dbo.Intervals; CREATE TABLE dbo.Intervals (id INT NOT NULL, startdate DATE NOT NULL, enddate DATE NOT NULL); INSERT INTO dbo.Intervals(id, startdate, enddate) VALUES (1, "20120212", "20120220"), (2, "20120214", "20120312"), (3, "20120124", "20120201");

Эти интервалы могут представлять периоды активности, действительности или любые другие типы периодов. Задача заключается в том, чтобы при заданном периоде (с началом @from и @to конца), упаковать интервалы в нем. Иначе говоря, вам надо объединить перекрывающиеся и непосредственно прилегающие интервалы. Вот ожидаемый результат для приведенных тестовых данных при периоде с 1 января 2012 года до 31 декабря 2012 года:

В решении ниже, описанная в статье "Вспомогательные виртуальные таблицы чисел" функция GetNums, используется для генерации последовательности дат, которые укладываются в данный период. В коде определяется CTE по имени Dates, представляющее этот набор дат. Далее код соединяет CTE-выражение Dates (псевдоним D) в таблице Intervals (псевдоним I), сопоставляя каждой дате интервалы, которые ее содержат, используя такой предикат соединения: D.dt BETWEEN I.startdate AND I.enddate. Далее в коде используется описанная выше методика вычисления идентификатора групп (назовем его grp), который определяет диапазоны. На базе этого запроса в коде определяется CTE-выражение по имени Groups. Наконец внешний запрос группирует строки по grp и возвращает минимальную и максимальную дату каждого диапазона, которые и представляют собой границы упакованных интервалов. Вот код законченного решения:

DECLARE @from AS DATE = "20120101", @to AS DATE = "20121231"; WITH Dates AS (SELECT DATEADD(day, n-1, @from) AS dt FROM dbo.GetNums(1, DATEDIFF(day, @from, @to) + 1) AS Nums), Groups AS (SELECT D.dt, DATEADD(day, -1 * DENSE_RANK() OVER(ORDER BY D.dt), D.dt) AS grp FROM dbo.Intervals AS I JOIN Dates AS D ON D.dt BETWEEN I.startdate AND I.enddate) SELECT MIN(dt) AS rangestart, MAX(dt) AS rangeend FROM Groups GROUP BY grp;

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

Есть версии задачи нахождения диапазонов, которые намного сложнее базовой версии. Допустим, к примеру, что нужно игнорировать пробелы меньшие или равные определенному размеру, например в числовой последовательности нас не интересуют пробелы 2 и меньше. Тогда ожидаемый результат будет таким:

Заметьте, что значения 7, 8, 9 и 11 все являются частью одного диапазона с началом в 7 и концом в 11. Пробел между 9 и 11 игнорируется, потому что он меньше 2.

Для решения этой задачи можно использовать функции LAG и LEAD. Сначала определяем CTE но имени C1, в котором запрос таблицы T1 вычисляет следующие два атрибута: isstart и isend. Атрибут isstart является флагом, который равен единице, когда значение последовательности является первым в диапазоне, и нулю в противном случае. Значение не является первым значением в диапазоне, если разница между col1 и предыдущим значением (полученным с применением функции LAG) меньше или равна 2, в противном случае это первое значение диапазона. Аналогично, значение не является последним значением в диапазоне, если разница следующим значением (полученным с применением функции LEAD) и col1 меньше или равна 2, в противном случае это последнее значение диапазона.

Затем в коде определяется CTE по имени C2, которое отбирает только строки, в которых значения последовательности являются началом или концом диапазона. С помощью функции LEAD выделяются пары начала и конца каждого диапазона. Это достигается за счет использования выражения 1 - isend в качестве смещения функции LEAD. Это означает, что если текущая строка, представляющая начало диапазона, одновременно представляет ею конец, то смещение равно нулю, иначе оно равно единице. Наконец внешний запрос просто отбирает из результатов C2 только те строки, в которых isstart равно единице 1. Вот код законченною решения.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Любой запрос, создаваемый для работ в БД, упрощает допуск к нужной информации. В предыдущей записи я говорил об общих операторах условий. В этой же записи я поговорю об операторах, которые позволят создавать запросы, способные выдать более подробную интересующую информацию, которую в то же, запросами с операторами AND, OR не так просто найти.
Одним из специальных операторов является IN . Данный оператор позволяет задавать необходимый диапазон отображения нужной информации. Вернёмся к данным по дожникам

Debtors

Предположим, необходимо выбрать всех должников города Ставрополь или Татарка. По аналогии с предыдущей записью, нужно было бы использовать запрос
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 в подобного рода запросах, воспринимает только ту длину поисковых строк, которые заданы. Другими словами, длина строки "П" в запросе составляет один символ, а длина строки "Прени" и "Павлова" в базе данных - пять и семь соответственно. А вот фамилия "И ванов" попадает в диапазон, поскольку диапазон начинается с И , как начала, длиной от одного символа.