BETWEEN Operator in SQL

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

SELECT *
FROM Students
WHERE age BETWEEN 18 AND 25;

This returns students whose age is from 18 through 25, including 18 and 25.

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

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

SELECT *
FROM Students
WHERE age BETWEEN 18 AND 25;

The result can include ages 18, 19, 20, 21, 22, 23, 24, and 25.

BETWEEN includes both boundary values.

SELECT *
FROM Students
WHERE age BETWEEN 20 AND 25;

Age 20 and age 25 are both included.

BETWEEN is useful for finding fees within a range.

SELECT name, course, fee
FROM Students
WHERE fee BETWEEN 5000 AND 15000;

This includes fees from 5000 through 15000.

SELECT *
FROM Students
WHERE fee BETWEEN 7500.00 AND 12500.00;

This finds records whose fee falls within the specified decimal range.

BETWEEN can also be used with DATE values.

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

This selects dates from January 1 through June 30, 2026.

SELECT name, admission_date
FROM Students
WHERE admission_date
BETWEEN '2026-04-01' AND '2026-04-30';

This can be used to retrieve records whose date falls within April 2026.

BETWEEN can also compare strings according to the database's collation and sorting rules.

SELECT *
FROM Students
WHERE name BETWEEN 'A' AND 'M';

Text ranges can be database and collation dependent, so numeric and date ranges are generally easier to reason about.

Use NOT BETWEEN when you want values outside a range.

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

This returns ages below 18 or above 25 for non-NULL values.

SELECT *
FROM Students
WHERE fee NOT BETWEEN 5000 AND 15000;

This returns records whose fee is outside the range 5000 to 15000.

This query:

SELECT *
FROM Students
WHERE age BETWEEN 18 AND 25;

is equivalent for ordinary non-NULL values to:

SELECT *
FROM Students
WHERE age >= 18
AND age <= 25;

BETWEEN provides a shorter and readable way to express a range.

BETWEEN already uses the keyword AND to define its range. It can also be combined with another AND condition.

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

This finds Python students aged 18 through 25.

SELECT *
FROM Students
WHERE age BETWEEN 18 AND 25
OR age BETWEEN 30 AND 35;

This finds students whose age is in either of the two ranges.

SELECT *
FROM Students
WHERE age BETWEEN 18 AND 25
AND fee BETWEEN 5000 AND 15000;

Both the age and fee must fall within their respective ranges.

BETWEEN is normally used as part of a WHERE condition.

SELECT name, age
FROM Students
WHERE age BETWEEN 20 AND 30;

SELECT name, age, fee
FROM Students
WHERE age BETWEEN 18 AND 25
ORDER BY age ASC;

The records within the range are sorted by age in ascending order.

SELECT name, age, fee
FROM Students
WHERE fee BETWEEN 5000 AND 20000
ORDER BY fee DESC;

This returns matching records with the highest fee first.

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

In MySQL, this returns up to five matching records.

BETWEEN and LIKE can be used together when different conditions are needed.

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

This finds students aged 18 through 25 whose names start with A.

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

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

SELECT *
FROM Students
WHERE age BETWEEN 18 AND 25
AND course NOT IN ('Tally', 'PHP');

This excludes students enrolled in Tally and PHP while keeping the age within the specified range.

If the value being tested is NULL, the BETWEEN comparison does not produce a true result.

SELECT *
FROM Students
WHERE age BETWEEN 18 AND 25;

Rows with NULL age are not returned by this condition.

SELECT name, course, fee
FROM Students
WHERE fee BETWEEN 10000 AND 20000;

This is useful when creating fee-related reports.

SELECT name, age, course
FROM Students
WHERE age BETWEEN 18 AND 30;

This returns students whose age falls within the specified range.

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

This can be used to find admissions made during the first quarter of 2026.

BETWEEN is commonly useful for filtering reports by ranges such as:

• Age range
• Fee range
• Salary range
• Date range
• Product price range
• Marks range

Remember that BETWEEN includes both boundaries.

SELECT *
FROM Students
WHERE age BETWEEN 18 AND 25;

Age 18 and age 25 are included.

If you want to exclude the boundaries, use comparison operators instead. For example:

SELECT *
FROM Students
WHERE age > 18
AND age < 25;

Suppose an institute wants students who are between 18 and 30 years old, are enrolled in Python or Java, and have paid between ₹5,000 and ₹20,000.

SELECT name, age, course, fee
FROM Students
WHERE age BETWEEN 18 AND 30
AND course IN ('Python', 'Java')
AND fee BETWEEN 5000 AND 20000;

SELECT name, age, course, city, fee, admission_date
FROM Students
WHERE age BETWEEN 18 AND 30
AND fee BETWEEN 5000 AND 20000
AND admission_date BETWEEN '2026-01-01' AND '2026-12-31'
AND course IN ('Python', 'Java')
ORDER BY fee DESC;

This query:

• Finds students aged 18 through 30.
• Filters fees from ₹5,000 through ₹20,000.
• Checks admissions within the year 2026.
• Allows Python or Java courses.
• Sorts the result by fee in descending order.