BETWEEN: примеры (SQL)

Примеры использования оператора BETWEEN

Пример с числовыми значениями:

SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE UnitPrice BETWEEN 10.00 AND 20.00;

ProductID  ProductName          UnitPrice
---------- -------------------- ----------
1          Product A            10.00
2          Product B            15.50
3          Product C            20.00

Пример с датами:

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31';

OrderID    OrderDate
---------- -----------------------
10248      2023-01-01 00:00:00.000
10249      2023-01-10 00:00:00.000
10250      2023-01-31 00:00:00.000

Пример с символьными строками:

SELECT CustomerID, CompanyName
FROM Customers
WHERE CompanyName BETWEEN 'A' AND 'C';

CustomerID CompanyName
---------- ---------------
ALFKI      Alfreds
ANATR      Ana Trujillo
ANTON      Antonio Moreno

Использование с отрицанием NOT:

SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE UnitPrice NOT BETWEEN 10 AND 20;

ProductID  ProductName          UnitPrice
---------- -------------------- ----------
4          Product D            5.99
5          Product E            25.00

Альтернативные операторы и функции в MS SQL

В MS SQL существуют другие способы проверки вхождения значения в диапазон.

• Операторы >= и <=. Прямая замена BETWEEN комбинацией этих операторов. Используется, когда необходима проверка на невключительные границы или сложная логика. BETWEEN является синтаксическим сахаром для выражение >= начало AND выражение <= конец.
• Функция IIF или выражение CASE. Позволяют реализовать более сложную условную логику, включая проверку диапазонов, но с менее лаконичным синтаксисом.
• Оператор IN. Подходит для проверки вхождения значения в дискретный набор, но не для непрерывных диапазонов.

Выбор между BETWEEN и комбинацией >=/<= — вопрос стиля и читаемости. BETWEEN предпочтительнее для простых включительных диапазонов, так как явно указывает на намерение проверить интервал.

Оператор BETWEEN в других СУБД

Оператор BETWEEN является стандартом SQL и поддерживается большинством СУБД, но есть нюансы.

MySQL, PostgreSQL, SQLite: Работает аналогично MS SQL, проверяя вхождение во включительный диапазон. Синтаксис идентичен.

-- PostgreSQL/MySQL пример
SELECT * FROM employees WHERE salary BETWEEN 3000 AND 5000;

Oracle: Поведение аналогично, но при работе с датами и TIMESTAMP важно учитывать время. Для сравнения только дат используется функция TRUNC.

-- Oracle пример с датой
SELECT * FROM orders
WHERE order_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-01-31', 'YYYY-MM-DD');

Sybase ASE/SQL Anywhere: Поддерживает стандартный синтаксис BETWEEN.

Главное отличие между СУБД заключается в обработке типов данных, особенно дат и строк. В Oracle и PostgreSQL сравнение строк зависит от параметров сортировки. В SQLite сравнение строк по умолчанию регистрозависимо.

Распространенные ошибки при использовании

1. Неверный порядок границ. Если начальное значение больше конечного, диапазон становится пустым.

-- Неверно: диапазон пуст
SELECT * FROM t WHERE id BETWEEN 20 AND 10;

-- Результат: 0 строк

2. Путаница с включением границ для дат с временем. Для типа DATETIME значение '2023-01-31' подразумевает время 00:00:00. Запись за 31 января 2023 года в 14:00 не попадет в диапазон, если верхняя граница указана как '2023-01-31'.

-- Ошибка: пропуск данных из-за времени
SELECT * FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31';

Решение — использовать открытую верхнюю границу или функции для приведения даты.

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

-- Ошибка, если Price хранится как строка
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

4. Обработка NULL. Если любая часть выражения (проверяемое значение, начало или конец диапазона) — NULL, результат всего условия — UNKNOWN, и строка не включается в результат.

Изменения в последних версиях MS SQL Server

Сам оператор BETWEEN не претерпел значительных изменений в последних версиях SQL Server. Его базовое поведение остается стабильным и соответствует стандарту SQL.

Однако, на производительность и корректность работы с BETWEEN могут влиять общие улучшения оптимизатора запросов и обработки индексов. Например, начиная с SQL Server 2016, улучшена работа с динамической статистикой и Cardinality Estimation, что может повлиять на выбор плана выполнения для запросов с BETWEEN.

Косвенно, появление новых типов данных, таких как DATETIME2 или пространственных данных, расширяет сферу применения оператора, но логика его работы с ними идентична работе со стандартными типами.

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

Расширенные примеры использования

Использование в выражении HAVING:

SELECT CustomerID, COUNT(OrderID) AS OrderCount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) BETWEEN 5 AND 10;

CustomerID  OrderCount
----------  -----------
ALFKI       6
ANATR       8

Сложное выражение в качестве аргумента:

SELECT OrderID, UnitPrice * Quantity AS Total
FROM [Order Details]
WHERE (UnitPrice * Quantity) BETWEEN 100 AND 500;

BETWEEN с подзапросом в качестве границы:

SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice BETWEEN 
    (SELECT MIN(UnitPrice) FROM Products) AND
    (SELECT AVG(UnitPrice) FROM Products);

Комбинирование с другими условиями:

SELECT * FROM Employees
WHERE (BirthDate BETWEEN '1950-01-01' AND '1960-01-01')
    AND (City = 'London' OR City = 'Seattle');

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

SELECT LastName, FirstName FROM Employees
WHERE LastName BETWEEN 'D' AND 'G';

LastName  FirstName
--------- ----------
Davolio   Nancy
Fuller    Andrew

Проверка вхождения значения из столбца в диапазон, заданный другими столбцами:

-- Проверка, попадает ли дата начала в период действия скидки
SELECT p.ProductName, d.StartDate, d.EndDate
FROM Products p
INNER JOIN Discounts d ON 1=1
WHERE '2023-06-15' BETWEEN d.StartDate AND d.EndDate;

Использование в CHECK-ограничении таблицы:

CREATE TABLE Products (
    ProductID int PRIMARY KEY,
    Price money NOT NULL,
    CONSTRAINT CK_Price_Range CHECK (Price BETWEEN 0 AND 10000)
);