MySQL BETWEEN

The BETWEEN operator checks whether a value falls within a specified range.

SELECT *
FROM students
WHERE age BETWEEN 18 AND 25;

This returns students whose age is from 18 through 25.

The basic syntax is:

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

value1 is the lower limit and value2 is the upper limit.

BETWEEN is commonly used with numeric columns.

SELECT *
FROM students
WHERE age BETWEEN 18 AND 25;

This selects ages from 18 to 25, including both 18 and 25.

The boundary values are included when using BETWEEN.

SELECT *
FROM students
WHERE age BETWEEN 20 AND 25;

This can match students aged 20, 21, 22, 23, 24, and 25.

BETWEEN can be used to find fees within a range.

SELECT *
FROM students
WHERE fee BETWEEN 10000 AND 20000;

This returns students whose fee is between 10,000 and 20,000, including both limits.

BETWEEN can also work with decimal values.

SELECT *
FROM students
WHERE fee BETWEEN 12500.50 AND 18500.75;

This filters fees within the specified decimal range.

BETWEEN can be used to filter records between two dates.

SELECT *
FROM students
WHERE admission_date
BETWEEN '2026-01-01' AND '2026-06-30';

This returns records with admission dates within the specified date range.

BETWEEN can also be used with DATETIME values.

SELECT *
FROM attendance
WHERE login_time
BETWEEN '2026-09-01 09:00:00'
AND '2026-09-01 18:00:00';

This selects records within the specified date and time range.

NOT BETWEEN finds values outside a specified range.

SELECT *
FROM students
WHERE age NOT BETWEEN 18 AND 25;

This returns students whose age is outside the range 18 to 25.

BETWEEN is normally used with the WHERE clause.

SELECT name, age
FROM students
WHERE age BETWEEN 18 AND 25;

Only students within the specified age range are returned.

BETWEEN can be combined with another condition using AND.

SELECT *
FROM students
WHERE age BETWEEN 18 AND 25
AND course = 'Python';

This returns Python students whose age is between 18 and 25.

BETWEEN can also be combined with OR.

SELECT *
FROM students
WHERE age BETWEEN 18 AND 20
OR age BETWEEN 25 AND 30;

This returns students in either age range.

You can use multiple BETWEEN conditions in one query.

SELECT *
FROM students
WHERE age BETWEEN 18 AND 25
AND fee BETWEEN 10000 AND 20000;

Both ranges must be satisfied.

Parentheses can make complex conditions easier to understand.

SELECT *
FROM students
WHERE
(course = 'Python' AND age BETWEEN 18 AND 25)
OR fee BETWEEN 20000 AND 30000;

The parentheses clearly group the first set of conditions.

BETWEEN can be combined with ORDER BY to sort the filtered results.

SELECT name, age, fee
FROM students
WHERE fee BETWEEN 10000 AND 20000
ORDER BY fee DESC;

The matching records are sorted from highest fee to lowest fee.

LIMIT can restrict the number of records returned by a BETWEEN query.

SELECT *
FROM students
WHERE age BETWEEN 18 AND 25
LIMIT 5;

This returns up to five matching records.

You do not need to select all columns.

SELECT id, name, course
FROM students
WHERE age BETWEEN 18 AND 25;

Only the selected columns are displayed.

BETWEEN and IN solve different types of filtering problems.

SELECT *
FROM students
WHERE age BETWEEN 18 AND 25
AND course IN ('Python', 'Java');

This selects students in the age range who are enrolled in Python or Java.

BETWEEN can be combined with LIKE for more specific searches.

SELECT *
FROM students
WHERE age BETWEEN 18 AND 25
AND name LIKE 'A%';

This finds students aged 18 to 25 whose names begin with A.

NOT BETWEEN is the common form used to exclude a range.

SELECT *
FROM students
WHERE NOT age BETWEEN 18 AND 25;

This excludes students whose age is between 18 and 25.

BETWEEN can be used with UPDATE to modify records within a range.

UPDATE students
SET fee = fee + 1000
WHERE fee BETWEEN 10000 AND 15000;

This increases the fee for students whose current fee is within the specified range.

BETWEEN can be used with DELETE to remove records within a range.

DELETE FROM students
WHERE age BETWEEN 10 AND 15;

A practical fee search can be written as:

SELECT id, name, course, fee
FROM students
WHERE fee BETWEEN 10000.00 AND 20000.00
ORDER BY fee;

This displays students whose fee falls within the selected range.

BETWEEN is useful for searching records between two dates.

SELECT id, name, admission_date
FROM students
WHERE admission_date
BETWEEN '2026-01-01' AND '2026-03-31';

This can be useful for finding students admitted during a particular period.

• Forgetting that BETWEEN includes both boundary values
• Using the lower and upper limits incorrectly
• Using incorrect date formats
• Forgetting quotes around date values
• Confusing BETWEEN with IN
• Using BETWEEN without checking the column's data type
• Using BETWEEN in DELETE without checking the affected records

A simple BETWEEN workflow is:

1. Identify the column to filter.
2. Choose the lower limit.
3. Choose the upper limit.
4. Write the BETWEEN condition.
5. Test the query with SELECT.
6. Add ORDER BY or LIMIT if needed.

SELECT name, course, fee
FROM students
WHERE fee BETWEEN 10000 AND 20000
ORDER BY fee DESC;

BETWEEN can make range conditions easier to read.

Using comparison operators:

WHERE age >= 18
AND age <= 25;

Using BETWEEN:

WHERE age BETWEEN 18 AND 25;

For this numeric range, both express the same inclusive boundaries.

Suppose we want students whose age is between 18 and 25 and whose fee is between 10,000 and 20,000.

SELECT id, name, course, age, fee
FROM students
WHERE age BETWEEN 18 AND 25
AND fee BETWEEN 10000 AND 20000
ORDER BY fee DESC;

This applies two different ranges to the same result.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    course VARCHAR(100),
    age INT,
    fee DECIMAL(10,2),
    admission_date DATE
);

INSERT INTO students
(name, course, age, fee, admission_date)
VALUES
('Rahul', 'Python', 22, 15000.00, '2026-01-15'),
('Priya', 'Java', 21, 18000.00, '2026-02-10'),
('Amit', 'Python', 24, 12000.00, '2026-03-05'),
('Neha', 'PHP', 17, 10000.00, '2026-04-12'),
('Ravi', 'JavaScript', 26, 22000.00, '2026-05-20');

SELECT id, name, course, age, fee
FROM students
WHERE age BETWEEN 18 AND 25
AND fee BETWEEN 10000 AND 20000
ORDER BY fee DESC;

This query finds students whose age and fee both fall within the specified ranges.