CodeCapsule

SQL BETWEEN operator

by

Here is a clear and practical guide to the SQL BETWEEN operator, with examples for numbers, dates, text, and best-practice notes.

✅ What the BETWEEN Operator Does

BETWEEN is used to filter values within a range, including the lower and upper bounds.

Basic syntax:

column_name BETWEEN value1 AND value2

This is equivalent to:

column_name >= value1 AND column_name <= value2

📌 Numeric Examples

1. Find prices between 10 and 50

SELECT *
FROM products
WHERE price BETWEEN 10 AND 50;

2. Find scores outside a range

SELECT *
FROM results
WHERE score NOT BETWEEN 60 AND 90;

📅 Date Examples

1. Find orders created between two dates

SELECT *
FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

2. Safer version for end-of-day timestamp

If created_at includes timestamps:

WHERE created_at >= '2024-01-01'
AND created_at < '2024-02-01'

(Recommended for PostgreSQL/MySQL.)

🔤 Text Examples (lexicographical order)

BETWEEN can compare text alphabetically.

SELECT *
FROM customers
WHERE last_name BETWEEN 'A' AND 'M';

This returns last names starting with A through M (inclusive).

🧠 Important Behavior Notes

✔ BETWEEN is inclusive (>= and <=)
✔ Works on numbers, dates, and text
✔ Avoid using it on DATETIME unless careful with upper bound
✔ Better readability than writing two comparisons

🔥 Real-World Use Cases

Get all employees with salaries in a range

SELECT name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 90000;

Get log entries from the last 24 hours

SELECT *
FROM logs
WHERE log_time BETWEEN NOW() - INTERVAL '1 day' AND NOW();

Filter rows by alphabetical range

SELECT *
FROM products
WHERE sku BETWEEN 'A100' AND 'A999';

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *