SQL - BETWEEN Operator
The BETWEEN operator filters records within an inclusive range. The basic syntax follows this pattern:
Key Insights
- The BETWEEN operator provides inclusive range filtering for numeric, date, and text values, offering cleaner syntax than combining >= and <= operators
- Performance characteristics vary significantly between clustered and non-clustered indexes, with range scans on indexed columns typically outperforming OR-based alternatives
- Text-based BETWEEN operations use lexicographic ordering and are case-sensitive in most databases, requiring careful consideration of collation settings
Understanding BETWEEN Syntax
The BETWEEN operator filters records within an inclusive range. The basic syntax follows this pattern:
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
This is functionally equivalent to:
SELECT column_name(s)
FROM table_name
WHERE column_name >= value1 AND column_name <= value2;
Both boundaries are inclusive. Here’s a practical example using a products table:
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(10,2),
stock_quantity INT,
created_date DATE
);
INSERT INTO products VALUES
(1, 'Laptop', 899.99, 45, '2024-01-15'),
(2, 'Mouse', 24.99, 150, '2024-01-20'),
(3, 'Keyboard', 79.99, 89, '2024-02-01'),
(4, 'Monitor', 299.99, 32, '2024-02-10'),
(5, 'Webcam', 129.99, 67, '2024-03-05');
-- Find products priced between $50 and $300
SELECT product_name, price
FROM products
WHERE price BETWEEN 50.00 AND 300.00;
Results:
product_name | price
-------------|--------
Keyboard | 79.99
Monitor | 299.99
Webcam | 129.99
Numeric Range Queries
Numeric BETWEEN operations are straightforward and perform well with proper indexing:
-- Find products with stock between 30 and 100 units
SELECT product_name, stock_quantity
FROM products
WHERE stock_quantity BETWEEN 30 AND 100
ORDER BY stock_quantity DESC;
-- Multiple BETWEEN conditions
SELECT product_name, price, stock_quantity
FROM products
WHERE price BETWEEN 20.00 AND 150.00
AND stock_quantity BETWEEN 40 AND 200;
For performance optimization, create an index on columns frequently used in BETWEEN clauses:
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_products_stock ON products(stock_quantity);
When dealing with calculated ranges, consider this pattern:
-- Find products within 20% of target price ($100)
DECLARE @target_price DECIMAL(10,2) = 100.00;
DECLARE @variance DECIMAL(10,2) = @target_price * 0.20;
SELECT product_name, price,
ABS(price - @target_price) as price_difference
FROM products
WHERE price BETWEEN (@target_price - @variance)
AND (@target_price + @variance);
Date Range Filtering
Date-based BETWEEN queries are common in reporting and analytics:
-- Products created in February 2024
SELECT product_name, created_date
FROM products
WHERE created_date BETWEEN '2024-02-01' AND '2024-02-28';
-- Using date functions for dynamic ranges
SELECT product_name, created_date
FROM products
WHERE created_date BETWEEN DATEADD(month, -1, GETDATE())
AND GETDATE();
Critical consideration: When working with datetime columns (not just dates), BETWEEN can produce unexpected results:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_datetime DATETIME,
total_amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
(1, '2024-02-15 08:30:00', 150.00),
(2, '2024-02-15 14:45:00', 200.00),
(3, '2024-02-15 23:59:59', 175.00),
(4, '2024-02-16 00:00:01', 225.00);
-- This misses order_id 4
SELECT order_id, order_datetime
FROM orders
WHERE order_datetime BETWEEN '2024-02-15' AND '2024-02-16';
The query above converts ‘2024-02-16’ to ‘2024-02-16 00:00:00’, excluding any orders after midnight. Use this pattern instead:
-- Correct approach for full day ranges
SELECT order_id, order_datetime
FROM orders
WHERE order_datetime >= '2024-02-15'
AND order_datetime < '2024-02-17';
-- Or use explicit time components
SELECT order_id, order_datetime
FROM orders
WHERE order_datetime BETWEEN '2024-02-15 00:00:00'
AND '2024-02-16 23:59:59.999';
Text-Based BETWEEN Operations
BETWEEN works with string values using lexicographic (dictionary) ordering:
-- Products with names between 'K' and 'N'
SELECT product_name
FROM products
WHERE product_name BETWEEN 'K' AND 'N'
ORDER BY product_name;
Results include “Keyboard” and “Monitor” but not “Mouse” (M < N but “Mo” > “N” lexicographically).
Understanding alphabetical ranges:
-- Find all products starting with 'K', 'L', or 'M'
SELECT product_name
FROM products
WHERE product_name BETWEEN 'K' AND 'N' -- 'N' is exclusive boundary
ORDER BY product_name;
-- More explicit range
SELECT product_name
FROM products
WHERE product_name BETWEEN 'K' AND 'Mzzzzzz'
ORDER BY product_name;
Case sensitivity matters:
-- Case-sensitive comparison (default in many databases)
SELECT product_name
FROM products
WHERE product_name BETWEEN 'k' AND 'n'; -- May return no results
-- Case-insensitive approach
SELECT product_name
FROM products
WHERE UPPER(product_name) BETWEEN 'K' AND 'N';
NOT BETWEEN for Exclusion
Invert the logic to exclude ranges:
-- Products NOT in the mid-price range
SELECT product_name, price
FROM products
WHERE price NOT BETWEEN 50.00 AND 200.00;
This returns products priced below $50 or above $200. It’s equivalent to:
SELECT product_name, price
FROM products
WHERE price < 50.00 OR price > 200.00;
Practical use case combining inclusion and exclusion:
-- Products in stock but not overstocked
SELECT product_name, stock_quantity,
CASE
WHEN stock_quantity BETWEEN 1 AND 30 THEN 'Low Stock'
WHEN stock_quantity BETWEEN 31 AND 100 THEN 'Normal Stock'
ELSE 'Overstocked'
END as stock_status
FROM products
WHERE stock_quantity NOT BETWEEN 0 AND 0; -- Exclude out-of-stock
Performance Considerations
BETWEEN can leverage indexes efficiently when used on indexed columns:
-- Check execution plan
EXPLAIN SELECT product_name, price
FROM products
WHERE price BETWEEN 50.00 AND 300.00;
Performance comparison with OR-based alternatives:
-- BETWEEN (index range scan)
SELECT * FROM products
WHERE product_id BETWEEN 1000 AND 2000;
-- Multiple OR conditions (multiple index seeks)
SELECT * FROM products
WHERE product_id = 1000 OR product_id = 1001 OR product_id = 1002;
For large ranges, BETWEEN typically outperforms multiple OR conditions because it performs a single index range scan rather than multiple seeks.
Avoid using functions on the filtered column, as this prevents index usage:
-- Bad: Function prevents index usage
SELECT * FROM products
WHERE YEAR(created_date) BETWEEN 2023 AND 2024;
-- Good: Allows index usage
SELECT * FROM products
WHERE created_date BETWEEN '2023-01-01' AND '2024-12-31';
Handling NULL Values
BETWEEN does not match NULL values:
INSERT INTO products (product_id, product_name, price, stock_quantity)
VALUES (6, 'Unknown Item', NULL, 10);
-- This excludes the NULL price
SELECT product_name, price
FROM products
WHERE price BETWEEN 0 AND 1000;
-- Include NULLs explicitly
SELECT product_name, price
FROM products
WHERE price BETWEEN 0 AND 1000 OR price IS NULL;
The BETWEEN operator provides clean, readable syntax for range queries while maintaining good performance characteristics when properly indexed. Understanding its inclusive nature, behavior with different data types, and interaction with NULL values ensures correct query results in production systems.