SQL - BETWEEN Operator

SQL - The BETWEEN Operator

1. The SQL BETWEEN Operator

The BETWEEN operator is a logical operator in SQL used to select values within a specified range. The range is inclusive, meaning it includes the start and end values. It simplifies queries that would otherwise require two separate conditions with AND.

WHERE column >= value1 AND column <= value2 is equivalent to WHERE column BETWEEN value1 AND value2.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

The values can be numbers, text, or dates.

2. Practical Examples with Numbers, Text, and Dates

Let’s set up a sample Products table for our examples.

CREATE TABLE Products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_date DATE NOT NULL
);

INSERT INTO Products VALUES
    (1, 'Laptop', 1200.00, '2023-01-15'),
    (2, 'Mouse', 25.00, '2023-01-20'),
    (3, 'Keyboard', 75.00, '2023-02-10'),
    (4, 'Monitor', 300.00, '2023-02-25'),
    (5, 'Webcam', 50.00, '2023-03-05');

Example: Numeric Range

To find all products with a price between $50 and $300 (inclusive):

SELECT * FROM Products WHERE price BETWEEN 50.00 AND 300.00;

Output:

id	name	price	stock_date
3	Keyboard	75.00	2023-02-10
4	Monitor	300.00	2023-02-25
5	Webcam	50.00	2023-03-05

Example: Text Range

To find products with names starting with letters from ‘K’ to ‘M’. Be aware that this is a lexicographical comparison.

SELECT * FROM Products WHERE name BETWEEN 'K' AND 'Mouse';

This query will include ‘Keyboard’, ‘Laptop’, and ‘Monitor’, but also ‘Mouse’ because ‘Mouse’ is lexicographically between ‘K’ and itself. A common pitfall is that BETWEEN 'K' AND 'M' would not include ‘Monitor’ or ‘Mouse’. To get all names starting with K, L, or M, a LIKE condition is often clearer: WHERE name LIKE 'K%' OR name LIKE 'L%' OR name LIKE 'M%'.

Example: Date Range

To find all products stocked in February 2023:

SELECT * FROM Products WHERE stock_date BETWEEN '2023-02-01' AND '2023-02-28';

Output:

id	name	price	stock_date
3	Keyboard	75.00	2023-02-10
4	Monitor	300.00	2023-02-25

3. The NOT BETWEEN Operator

The NOT BETWEEN operator does the opposite: it selects values that are outside the specified range.

SELECT * FROM Products WHERE price NOT BETWEEN 50.00 AND 300.00;

This is equivalent to WHERE price < 50.00 OR price > 300.00.

Output:

id	name	price	stock_date
1	Laptop	1200.00	2023-01-15
2	Mouse	25.00	2023-01-20

4. Combining BETWEEN with other clauses

With IN Operator

You can combine BETWEEN with an IN clause to further filter results. Find products priced between $20 and $100 that are either a ‘Mouse’ or ‘Keyboard’.

SELECT * FROM Products
WHERE price BETWEEN 20.00 AND 100.00
AND name IN ('Mouse', 'Keyboard');

With UPDATE Statement

Increase the price by 10% for all products stocked in January.

UPDATE Products
SET price = price * 1.10
WHERE stock_date BETWEEN '2023-01-01' AND '2023-01-31';

With DELETE Statement

Delete all products with a price between $70 and $80.

DELETE FROM Products
WHERE price BETWEEN 70.00 AND 80.00;

5. Performance Considerations

For BETWEEN to be efficient on large tables, the column used in the WHERE clause should have an index. An index allows the database to quickly locate the start of the range without scanning the entire table.

-- Creating an index on the price column will speed up price range queries.
CREATE INDEX idx_products_price ON Products(price);

Queries using BETWEEN on an indexed column are generally very fast and are a preferred way to query for ranges.