SQL Between

The SQL BETWEEN operator is a filter used in a WHERE clause to select values within a specific, inclusive range.
Instead of writing two separate mathematical conditions combined with an AND operator (like age >= 21 AND age <= 35), BETWEEN provides a much cleaner, shorthand syntax. It works seamlessly with numbers, text, and dates.

Basic Syntax

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

⚠️ Important Note: The BETWEEN operator is inclusive. This means that both value1 (the start value) and value2 (the end value) are included in the results.

2. Real-World Examples## Numeric Ranges (Filtering Numbers)

-- Finds all products that cost anywhere from ₹10 to ₹50, including 10 and 50
SELECT product_name, price FROM products
WHERE price BETWEEN 10 AND 50;

Date Ranges (Filtering Dates)

When filtering dates, always format the text string as YYYY-MM-DD (or the specific format required by your database).

-- Extracts all orders placed throughout the entire year of 2026
SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-12-31';

Text Ranges (Sorting Alphabetically)

BETWEEN can sort string records alphabetically. However, keep in mind that it checks up to the literal character match.

-- Finds products whose names begin with any letter from 'A' through 'K'
SELECT product_name FROM products
WHERE product_name BETWEEN 'A' AND 'K';

Note: An item named exactly "K" would be included, but an item named "King" would be excluded because "King" is alphabetically further than the standalone character "K". To include all 'K' words, you would write BETWEEN 'A' AND 'L'.

Reversing the Logic (NOT BETWEEN)

To find records that fall outside of a specific range, simply add the NOT keyword.

-- Displays products that are either cheaper than ₹10 or more expensive than ₹50
SELECT product_name, price FROM products
WHERE price NOT BETWEEN 10 AND 50;

How it Simplifies Code

Behind the scenes, the database engine treats BETWEEN exactly like a standard comparison chain.

-- These two statements execute identically:
WHERE price BETWEEN 10 AND 50;WHERE price >= 10 AND price <= 50;