MySQL BETWEEN Operator

The BETWEEN operator in MySQL filters rows where a column value falls within a specified range. The range is inclusive, meaning both the start and end values are included in the results. It works with numbers, dates, and text values.

Syntax

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

This returns all rows where column_name is greater than or equal to value1 and less than or equal to value2.

Sample Table

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(8,2),
    stock_date DATE
);

INSERT INTO products VALUES (1, 'Notebook', 'Stationery', 45.00, '2024-01-10');
INSERT INTO products VALUES (2, 'Pen Drive', 'Electronics', 450.00, '2024-03-15');
INSERT INTO products VALUES (3, 'Calculator', 'Electronics', 220.00, '2024-02-20');
INSERT INTO products VALUES (4, 'Ruler', 'Stationery', 15.00, '2024-04-05');
INSERT INTO products VALUES (5, 'Laptop Stand', 'Electronics', 1200.00, '2024-05-01');
INSERT INTO products VALUES (6, 'Marker Set', 'Stationery', 120.00, '2024-01-25');

Example: BETWEEN with Numbers

SELECT product_name, price
FROM products
WHERE price BETWEEN 100 AND 500;

Result:

product_name | price
-------------+--------
Pen Drive    | 450.00
Calculator   | 220.00
Marker Set   | 120.00

Products priced exactly 100 or 500 would also be included (inclusive range).

Example: BETWEEN with Dates

SELECT product_name, stock_date
FROM products
WHERE stock_date BETWEEN '2024-01-01' AND '2024-03-31';

Result:

product_name | stock_date
-------------+-----------
Notebook     | 2024-01-10
Pen Drive    | 2024-03-15
Calculator   | 2024-02-20
Marker Set   | 2024-01-25

Example: BETWEEN with Text

With text values, BETWEEN uses alphabetical (lexicographic) order.

SELECT product_name
FROM products
WHERE product_name BETWEEN 'C' AND 'P';

Returns products whose names start between C and P alphabetically.

NOT BETWEEN

NOT BETWEEN returns rows where the value falls outside the specified range.

SELECT product_name, price
FROM products
WHERE price NOT BETWEEN 100 AND 500;

Result:

product_name | price
-------------+---------
Notebook     | 45.00
Ruler        | 15.00
Laptop Stand | 1200.00

BETWEEN vs Comparison Operators

The two expressions below produce identical results:

-- Using BETWEEN
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- Using comparison operators
SELECT * FROM products WHERE price >= 100 AND price <= 500;

BETWEEN is simply a shorter and more readable way to express the same range condition.

Key Points

• BETWEEN filters rows within a range, including both boundary values.
• It works with numbers, dates, and text.
• NOT BETWEEN returns rows outside the specified range.
• For date ranges, use the format 'YYYY-MM-DD'.
• BETWEEN value1 AND value2 is equivalent to >= value1 AND <= value2.