MySQL BETWEEN

Last updated: October 19, 2026Oct 19, 2026

Many everyday questions are about a range: products priced from 500 to 1,000, employees aged 30 to 40, orders placed last week. In MySQL, the BETWEEN operator answers these questions in one short, readable condition.

This tutorial explains how BETWEEN works with numbers, dates, date-times and text, how NOT BETWEEN works, how NULL values behave, and how to use BETWEEN with indexes and from Java. You will also see the common mistakes and some practice exercises. Every result shown was produced by running the query on MySQL.

1. What is the BETWEEN Operator?

BETWEEN is a comparison operator. It checks whether a value lies inside a range that has a lower limit and an upper limit. It is used mostly in the WHERE clause to filter rows, but it works anywhere a condition is allowed.

Think of a shop's price filter where you choose "from 500 to 1,000". A product that costs exactly 500 or exactly 1,000 still appears in the results. BETWEEN behaves the same way: both limits are included.

2. Syntax and How It Works

SELECT column1, column2, ...
FROM table_name
WHERE expression BETWEEN low_value AND high_value;

MySQL reads this as two comparisons joined with AND:

-- These two conditions mean exactly the same thing
WHERE age BETWEEN 30 AND 40
WHERE age >= 30 AND age <= 40

• Inclusive: rows equal to low_value or high_value are returned.
• Low value first: write the smaller value before AND (see section 10).
• Any comparable type: numbers, DATE, DATETIME, TIME and text all work.
• Result: the condition is 1 (true), 0 (false) or NULL (unknown).

3. Sample Tables

All examples use two small tables. Run this script once to create them and follow along:

CREATE DATABASE IF NOT EXISTS company;
USE company;

CREATE TABLE employees (
  emp_id    INT PRIMARY KEY,
  name      VARCHAR(50),
  dept      VARCHAR(30),
  age       INT,
  salary    DECIMAL(10,2),
  joined_on DATE
);

INSERT INTO employees VALUES
(101, 'Aarav',  'Sales',     24, 32000, '2021-03-15'),
(102, 'Bhavna', 'HR',        31, 45000, '2019-07-01'),
(103, 'Chetan', 'IT',        29, 58000, '2022-01-10'),
(104, 'Divya',  'IT',        35, 72000, '2018-11-23'),
(105, 'Farhan', 'Finance',   42, 81000, '2016-05-30'),
(106, 'Gauri',  'Sales',     27, 39000, '2023-02-14'),
(107, 'Kiran',  'Marketing', 30, 50000, '2020-09-05'),
(108, 'Meera',  'Finance',   38, NULL,  '2024-06-17'),
(109, 'Nikhil', 'IT',        50, 95000, '2015-01-02'),
(110, 'Pooja',  'HR',        26, 36000, '2025-04-21');

CREATE TABLE orders (
  order_id   INT PRIMARY KEY,
  customer   VARCHAR(40),
  amount     DECIMAL(10,2),
  ordered_at DATETIME
);

INSERT INTO orders VALUES
(1, 'Ravi',   1200.00, '2026-03-01 09:15:00'),
(2, 'Sneha',   450.50, '2026-03-01 18:40:00'),
(3, 'Tarun',  3299.00, '2026-03-02 11:05:00'),
(4, 'Usha',    799.00, '2026-03-03 00:00:00'),
(5, 'Varun',  2150.75, '2026-03-03 14:30:00'),
(6, 'Ravi',    999.00, '2026-03-03 23:59:10'),
(7, 'Sneha',  5600.00, '2026-03-04 08:00:00');

The employees table:

Table: employees (10 rows)

The orders table:

Table: orders (7 rows)

4. BETWEEN with Numbers

Example 1: employees aged 30 to 40. Kiran (30) is on the lower limit and is included:

SELECT emp_id, name, age
FROM employees
WHERE age BETWEEN 30 AND 40;

Result: 4 rows

Example 2: decimal values work the same way. Salaries from 40,000 to 75,000, lowest first:

SELECT name, dept, salary
FROM employees
WHERE salary BETWEEN 40000 AND 75000
ORDER BY salary;

Result: 4 rows

Example 3: a range of IDs, which is handy when you process rows in batches:

SELECT emp_id, name, age
FROM employees
WHERE emp_id BETWEEN 103 AND 106;

Result: 4 rows

Example 4: orders worth 500 to 1,500:

SELECT order_id, amount
FROM orders
WHERE amount BETWEEN 500 AND 1500;

Result: 3 rows

5. NOT BETWEEN

NOT BETWEEN returns the rows outside the range: values smaller than the lower limit or bigger than the upper limit. The limits themselves are not returned.

-- Same as: WHERE age < 30 OR age > 40
SELECT emp_id, name, age
FROM employees
WHERE age NOT BETWEEN 30 AND 40;

Result: 6 rows

Salaries outside 40,000 to 75,000:

SELECT name, salary
FROM employees
WHERE salary NOT BETWEEN 40000 AND 75000;

Result: 5 rows

6. BETWEEN with Dates

Write dates as text in 'YYYY-MM-DD' format. MySQL converts them to dates before comparing. Employees who joined from 2020 to 2022:

SELECT name, joined_on
FROM employees
WHERE joined_on BETWEEN '2020-01-01' AND '2022-12-31'
ORDER BY joined_on;

Result: 3 rows

7. BETWEEN with DATETIME: the Last-Day Trap

A DATETIME column stores a time as well. When you write only a date, MySQL treats it as midnight at the start of that day. Look what happens when we ask for orders from 1 to 3 March:

SELECT order_id, customer, ordered_at
FROM orders
WHERE ordered_at BETWEEN '2026-03-01' AND '2026-03-03';

Result: 4 rows

Orders 5 and 6 were placed on 3 March, but they are missing. The upper limit '2026-03-03' really means '2026-03-03 00:00:00', so anything later that day is outside the range.

The fix: use a half-open range. Start at the first day (included) and stop before the day after the last day:

SELECT order_id, customer, ordered_at
FROM orders
WHERE ordered_at >= '2026-03-01'
  AND ordered_at <  '2026-03-04';

Result: 6 rows

Two other fixes you may see, and why the half-open range is better:

• BETWEEN '2026-03-01 00:00:00' AND '2026-03-03 23:59:59' works for whole seconds, but misses a value such as 23:59:59.500 if the column stores fractional seconds (DATETIME(3)).
• DATE(ordered_at) BETWEEN '2026-03-01' AND '2026-03-03' returns the right rows, but wrapping the column in a function stops MySQL from using an index on it (see section 13).

8. BETWEEN with Text

Text values are compared in dictionary (alphabetical) order, using the column's collation. Let's find names from 'B' to 'K':

SELECT name
FROM employees
WHERE name BETWEEN 'B' AND 'K'
ORDER BY name;

Result: 5 rows

Kiran starts with K, yet it is not returned. In a dictionary, a longer word comes after its own beginning, just as cart comes after car. So 'Kiran' is greater than 'K' and falls outside the range.

To include every name that starts with K, raise the upper limit, or stop just before the next letter:

-- Option 1: an upper limit that sorts after every 'K...' name
SELECT name FROM employees WHERE name BETWEEN 'B' AND 'Kz' ORDER BY name;

-- Option 2: stop before the next letter
SELECT name FROM employees WHERE name >= 'B' AND name < 'L' ORDER BY name;

Result of either query: 6 rows

9. BETWEEN and NULL Values

If the value being tested, or either limit, is NULL, the result is NULL (unknown), and WHERE keeps only rows whose condition is true. You can see the raw results by selecting the conditions directly:

SELECT 5    BETWEEN 1 AND 10 AS a,
       10   BETWEEN 1 AND 10 AS b,
       11   BETWEEN 1 AND 10 AS c,
       NULL BETWEEN 1 AND 10 AS d;

Result: 1 row

To include rows with a missing salary, ask for them explicitly:

SELECT name
FROM employees
WHERE salary BETWEEN 40000 AND 75000
   OR salary IS NULL;

Result: 5 rows

10. The Order of the Values Matters

BETWEEN does not swap the limits for you. age BETWEEN 40 AND 30 means age >= 40 AND age <= 30, which no number can satisfy. MySQL raises no error; you simply get nothing back:

SELECT name, age
FROM employees
WHERE age BETWEEN 40 AND 30;   -- wrong order

Result: Empty set (0 rows)

11. Using BETWEEN with Other Clauses

With AND: IT employees earning 50,000 to 80,000:

SELECT name, dept, age, salary
FROM employees
WHERE dept = 'IT'
  AND salary BETWEEN 50000 AND 80000;

Result: 2 rows

With OR: two separate ranges at once:

SELECT name, age
FROM employees
WHERE age BETWEEN 25 AND 29
   OR age BETWEEN 45 AND 55
ORDER BY age;

Result: 4 rows

With COUNT: how many employees are aged 28 to 40?

SELECT COUNT(*) AS mid_career
FROM employees
WHERE age BETWEEN 28 AND 40;

Result: 1 row

With CASE: put each employee into a salary band and count each band:

SELECT CASE
         WHEN salary BETWEEN 0     AND 39999 THEN 'Band A'
         WHEN salary BETWEEN 40000 AND 69999 THEN 'Band B'
         WHEN salary >= 70000                THEN 'Band C'
         ELSE 'Not set'
       END AS salary_band,
       COUNT(*) AS employees
FROM employees
GROUP BY salary_band
ORDER BY salary_band;

Result: 4 rows

With HAVING: departments whose average salary is between 40,000 and 70,000:

SELECT dept, ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY dept
HAVING AVG(salary) BETWEEN 40000 AND 70000
ORDER BY dept;

Result: 2 rows

With UPDATE: give a 10% raise to everyone who joined from 2015 to 2018:

UPDATE employees
SET salary = salary * 1.10
WHERE joined_on BETWEEN '2015-01-01' AND '2018-12-31';

Updated rows: 3

With DELETE: remove all orders placed on 1 March (a half-open range, because ordered_at is a DATETIME):

DELETE FROM orders
WHERE ordered_at >= '2026-03-01'
  AND ordered_at <  '2026-03-02';   -- deletes orders 1 and 2

12. BETWEEN vs Other Operators

BETWEEN and the >=/<= pair run at the same speed; MySQL treats them the same way. Choose whichever is easier to read.

13. BETWEEN and Indexes

An index on the column lets MySQL jump straight to the start of the range and read only the matching rows, instead of scanning the whole table. Add an index on joined_on and check the plan with EXPLAIN:

CREATE INDEX idx_joined_on ON employees (joined_on);

EXPLAIN SELECT name
FROM employees
WHERE joined_on BETWEEN '2020-01-01' AND '2022-12-31';

EXPLAIN result (main columns)

type = range means MySQL used the index to read only the 3 rows in the range. Now wrap the column in a function:

EXPLAIN SELECT name
FROM employees
WHERE YEAR(joined_on) BETWEEN 2020 AND 2022;

EXPLAIN result (main columns)

type = ALL is a full table scan: MySQL must calculate YEAR() for every row, so the index cannot help. Keep the column bare and put the calculation on the limits instead, as in the first query.

14. Using BETWEEN from Java (JDBC)

Range limits usually come from users, so pass them as parameters with a PreparedStatement. Never join user input into the SQL text; that opens the door to SQL injection. This program finds employees with a salary from 40,000 to 80,000 who joined from 2018 to 2022:

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;

public class EmployeeRangeSearch {

    private static final String URL = "jdbc:mysql://localhost:3306/company";

    public static void main(String[] args) throws SQLException {
        String sql = "SELECT name, salary, joined_on FROM employees "
                   + "WHERE salary BETWEEN ? AND ? "
                   + "AND joined_on BETWEEN ? AND ? "
                   + "ORDER BY salary";

        try (Connection con = DriverManager.getConnection(URL, "root", "your-password");
             PreparedStatement ps = con.prepareStatement(sql)) {

            ps.setBigDecimal(1, new BigDecimal("40000"));                 // lowest salary
            ps.setBigDecimal(2, new BigDecimal("80000"));                 // highest salary
            ps.setDate(3, Date.valueOf(LocalDate.of(2018, 1, 1)));        // joined from
            ps.setDate(4, Date.valueOf(LocalDate.of(2022, 12, 31)));      // joined until

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.printf("%-8s %10s  %s%n",
                            rs.getString("name"),
                            rs.getBigDecimal("salary"),
                            rs.getDate("joined_on"));
                }
            }
        }
    }
}

Output:

Bhavna     45000.00  2019-07-01
Kiran      50000.00  2020-09-05
Chetan     58000.00  2022-01-10
Divya      72000.00  2018-11-23

To run it, add the MySQL JDBC driver (com.mysql:mysql-connector-j) to your project and change the URL, user and password to match your server.

15. Common Mistakes

• Forgetting the time part. BETWEEN '2026-03-01' AND '2026-03-03' on a DATETIME column misses most of 3 March. Use >= '2026-03-01' AND < '2026-03-04'.
• Putting the bigger value first. BETWEEN 40 AND 30 returns no rows and gives no error.
• Expecting the limits to be excluded. BETWEEN always includes both ends. Use > and < if you need to exclude them.
• Overlapping ranges. BETWEEN 0 AND 100 and BETWEEN 100 AND 200 both include 100, so a value of 100 is counted twice. Use half-open ranges for buckets.
• Forgetting NULL. Rows with NULL appear in neither BETWEEN nor NOT BETWEEN results. Add OR column IS NULL when you need them.
• Text ranges that stop too early. BETWEEN 'A' AND 'M' leaves out Meera, because 'Meera' sorts after 'M'.
• Wrong date format. Use 'YYYY-MM-DD'. A value like '12/31/2022' is not a valid MySQL date: the query returns no rows and MySQL only shows a warning, not an error.
• Functions on the column. YEAR(col) BETWEEN ... or DATE(col) BETWEEN ... prevents index use. Compare the bare column with calculated limits.

16. Practice Exercises

Try these on the sample tables first, then open the solution to check your answer.

SELECT name, age
FROM employees
WHERE age BETWEEN 25 AND 30
ORDER BY age;

SELECT order_id, customer, amount
FROM orders
WHERE amount NOT BETWEEN 1000 AND 3000
ORDER BY amount;

SELECT name, joined_on
FROM employees
WHERE joined_on BETWEEN '2019-01-01' AND '2021-12-31'
ORDER BY joined_on;

-- ordered_at is a DATETIME, so use a half-open range
SELECT COUNT(*) AS orders, SUM(amount) AS total
FROM orders
WHERE ordered_at >= '2026-03-03'
  AND ordered_at <  '2026-03-04';

17. Summary

• BETWEEN low AND high keeps values from low to high, including both ends. It is the same as >= low AND <= high.
• NOT BETWEEN keeps values below low or above high.
• Always write the smaller value first; a reversed range returns no rows.
• It works for DATE columns as is. For DATETIME and TIMESTAMP, prefer >= start AND < next day.
• Text uses dictionary order, so 'Kiran' is after 'K'.
• NULL values are never returned by BETWEEN or NOT BETWEEN.
• Keep the column free of functions so an index can serve the range, and pass the limits as parameters from Java.