SQL BETWEEN: Filter Values within a Range

The SQL BETWEEN operator filters values within an inclusive range. Both boundary values are included.

SELECT id, name, class, mark
FROM student
WHERE mark BETWEEN 60 AND 75;

This is equivalent to:

SELECT id, name, class, mark
FROM student
WHERE mark >= 60
  AND mark <= 75;

For the Plus2net sample student table, marks of exactly 60 and 75 are included.

SQL BETWEEN Syntax Top ↑

SELECT column_list
FROM table_name
WHERE column_name BETWEEN lower_value AND upper_value;

The values are compared according to the datatype and comparison rules of the expression.

BETWEEN Includes Both Boundary Values Top ↑

This query includes marks equal to 60 and 75:

SELECT id, name, mark
FROM student
WHERE mark BETWEEN 60 AND 75
ORDER BY mark ASC, id ASC;

Lower and Upper Bound Order Matters Top ↑

In normal MySQL BETWEEN usage, the first boundary should be the lower value and the second should be the upper value.

SELECT id, name, mark
FROM student
WHERE mark BETWEEN 75 AND 60;

This is a valid query, but for ordinary numeric data it returns no rows because no value can be both greater than or equal to 75 and less than or equal to 60.

SQL NOT BETWEEN Top ↑

NOT BETWEEN returns values outside the inclusive range.

SELECT id, name, mark
FROM student
WHERE mark NOT BETWEEN 50 AND 100;

Because BETWEEN includes the boundaries, NOT BETWEEN means:

WHERE mark < 50
   OR mark > 100

BETWEEN with ORDER BY Top ↑

BETWEEN filters the rows; ORDER BY controls how those rows are displayed.

SELECT id, name, class, mark
FROM student
WHERE mark BETWEEN 60 AND 75
ORDER BY mark DESC, id ASC;

This displays the filtered marks from highest to lowest.

BETWEEN with Other Conditions Top ↑

Restrict the range to class Four:

SELECT id, name, class, mark
FROM student
WHERE mark BETWEEN 60 AND 75
  AND class = 'Four'
ORDER BY mark ASC, id ASC;

See AND / OR conditions for combining filters.

BETWEEN with IN and NOT IN Top ↑

Keep only classes Four and Seven:

SELECT id, name, class, mark
FROM student
WHERE mark BETWEEN 60 AND 75
  AND class IN (
      'Four',
      'Seven'
  );

Exclude those classes:

SELECT id, name, class, mark
FROM student
WHERE mark BETWEEN 60 AND 75
  AND class NOT IN (
      'Four',
      'Seven'
  );

See SQL IN and NOT IN for membership conditions and NULL cautions.

Count Rows within a Range Top ↑

Use COUNT() when only the number of matching rows is required:

SELECT COUNT(*) AS total_students
FROM student
WHERE mark BETWEEN 60 AND 75;

Count Range Matches by Group Top ↑

Combine BETWEEN with GROUP BY to count matching students in each class:

SELECT class,
       COUNT(*) AS total_students
FROM student
WHERE mark BETWEEN 60 AND 75
GROUP BY class
ORDER BY class;

Conditional range counts Top ↑

MySQL conditional aggregation can classify ranges:

SELECT class,
       SUM(
           CASE WHEN mark < 50
                THEN 1 ELSE 0 END
       ) AS grade_C,
       SUM(
           CASE WHEN mark BETWEEN 50 AND 70
                THEN 1 ELSE 0 END
       ) AS grade_B,
       SUM(
           CASE WHEN mark > 70
                THEN 1 ELSE 0 END
       ) AS grade_A
FROM student
GROUP BY class;

This version uses CASE, which is clearer and more portable than relying on MySQL's IF() for the same conditional-count idea.

BETWEEN with DATE Values Top ↑

BETWEEN works naturally with columns stored as SQL DATE values:

SELECT id, event_date, title
FROM events
WHERE event_date BETWEEN
      '2026-09-01'
      AND
      '2026-09-30'
ORDER BY event_date;

Because DATE values contain no time component, both September 1 and September 30 are fully included.

BETWEEN with DATETIME Values Top ↑

Be careful when a column contains a time component.

This query:

SELECT id, created_at
FROM orders
WHERE created_at BETWEEN
      '2026-09-01'
      AND
      '2026-09-30';

treats the upper literal as the start of September 30 when converted to a datetime value. Records later during September 30 can therefore be missed.

For a complete month range, a half-open interval is often safer:

SELECT id, created_at
FROM orders
WHERE created_at >= '2026-09-01 00:00:00'
  AND created_at <  '2026-10-01 00:00:00';

BETWEEN with Text Values Top ↑

BETWEEN can compare text values too, but the result follows the database's string comparison and collation rules.

SELECT id, name
FROM student
WHERE name BETWEEN
      'A'
      AND
      'M'
ORDER BY name;

For alphabetic range requirements, understand the active collation because case, accents and character ordering can affect comparisons.

BETWEEN and NULL Values Top ↑

If the tested value or a boundary is NULL, the comparison normally evaluates as unknown rather than true.

SELECT id, name, mark
FROM student3
WHERE mark BETWEEN 50 AND 75;

Rows where mark is NULL are not returned. See SQL NULL values.

BETWEEN with PHP PDO Prepared Statements Top ↑

When range boundaries come from a form or application, validate them and bind them as values.

<?php
require 'config.php';

$minimum=60;
$maximum=75;

if($minimum > $maximum){
    [$minimum,$maximum]=[$maximum,$minimum];
}

$stmt=$dbo->prepare(
    "SELECT id,name,class,mark
     FROM student
     WHERE mark BETWEEN :minimum AND :maximum
     ORDER BY mark ASC, id ASC"
);

$stmt->bindValue(
    ':minimum',
    $minimum,
    PDO::PARAM_INT
);

$stmt->bindValue(
    ':maximum',
    $maximum,
    PDO::PARAM_INT
);

$stmt->execute();

while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    echo htmlspecialchars(
        (string)$row['name'],
        ENT_QUOTES,
        'Windows-1252'
    ).' : '.
    (int)$row['mark'].
    '<br>';
}

The SQL remains the same whether it is executed from PHP, Python or another database client. The application layer is responsible for validating and supplying the range values safely.

BETWEEN Performance Notes Top ↑

• BETWEEN is a range condition and can benefit from an appropriate index on the compared column.
• The actual benefit depends on selectivity, table size, other WHERE conditions and the execution plan.
• A function applied to the indexed column can make index use less straightforward.
• For date/time ranges, compare the stored column directly to boundary values when possible instead of wrapping the column in a function.
• Use EXPLAIN when a production range query is unexpectedly slow.

Common SQL BETWEEN Mistakes Top ↑

Thinking BETWEEN excludes the boundaries Top ↑

BETWEEN is inclusive. BETWEEN 60 AND 75 includes both 60 and 75.

Reversing the lower and upper values Top ↑

A reversed ordinary range is valid SQL but usually returns no matching values. Normalize user-entered boundaries when necessary.

Using a date-only upper bound for a DATETIME column Top ↑

A date such as '2026-09-30' does not automatically mean every time on that day. For whole periods, a half-open range ending at the next period boundary is often safer.

Confusing BETWEEN with LIMIT Top ↑

BETWEEN filters by actual values. LIMIT restricts how many result rows are returned.

Expecting NULL values to match a numeric range Top ↑

NULL represents an unknown value and does not evaluate as true in an ordinary BETWEEN comparison.

Building range values directly into SQL text Top ↑

When boundaries come from an application, validate them and use prepared-statement parameters.

Related SQL Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: