SQL Between | Academic Block

SQL BETWEEN

Learn how to use the SQL BETWEEN operator to filter values within a specified range.

What is the BETWEEN Operator?

The BETWEEN operator is used to select values within a specified range. The range includes both the lower and upper boundary values.

BETWEEN can be used with numbers, text values, and dates. It is commonly used with the WHERE clause to filter records.

Basic BETWEEN Syntax

The basic syntax checks whether a value is between a lower value and an upper value.

WITH products(name, price) AS (
  VALUES
    ('Mouse', 300),
    ('Keyboard', 800),
    ('Webcam', 1200),
    ('Monitor', 1800),
    ('Laptop', 60000)
)
SELECT name, price
FROM products
WHERE price BETWEEN 500 AND 1500;

BETWEEN Includes Boundary Values

BETWEEN is inclusive. This means that values equal to both the minimum and maximum values are included in the result.

WITH scores(student, score) AS (
  VALUES
    ('Aman', 50),
    ('Neha', 60),
    ('Rahul', 70),
    ('Priya', 80),
    ('Karan', 90)
)
SELECT student, score
FROM scores
WHERE score BETWEEN 60 AND 80;

BETWEEN with Numbers

One of the most common uses of BETWEEN is filtering numeric values such as prices, salaries, scores, or quantities.

WITH employees(name, salary) AS (
  VALUES
    ('Aarav', 45000),
    ('Meera', 55000),
    ('Rohan', 65000),
    ('Anika', 75000),
    ('Vikram', 90000)
)
SELECT name, salary
FROM employees
WHERE salary BETWEEN 55000 AND 75000;

BETWEEN with Prices

You can use BETWEEN to find products whose prices fall within a particular range.

WITH products(name, price) AS (
  VALUES
    ('USB Cable', 250),
    ('Mouse', 600),
    ('Keyboard', 1200),
    ('Headphones', 1800),
    ('Speaker', 2500)
)
SELECT name, price
FROM products
WHERE price BETWEEN 500 AND 2000
ORDER BY price;

NOT BETWEEN

The NOT BETWEEN operator returns values that fall outside the specified range.

WITH products(name, price) AS (
  VALUES
    ('Mouse', 300),
    ('Keyboard', 800),
    ('Webcam', 1200),
    ('Monitor', 1800),
    ('Speaker', 3000)
)
SELECT name, price
FROM products
WHERE price NOT BETWEEN 800 AND 1800;

BETWEEN with AND

The BETWEEN operator can be combined with additional conditions using AND.

WITH employees(name, department, salary) AS (
  VALUES
    ('Aarav', 'IT', 65000),
    ('Meera', 'Finance', 72000),
    ('Rohan', 'Sales', 55000),
    ('Anika', 'IT', 82000),
    ('Vikram', 'HR', 60000)
)
SELECT name, department, salary
FROM employees
WHERE salary BETWEEN 60000 AND 80000
AND department IN ('IT', 'Finance');

BETWEEN with OR

Multiple BETWEEN conditions can be combined using the OR operator.

WITH products(name, price) AS (
  VALUES
    ('Mouse', 300),
    ('Keyboard', 900),
    ('Monitor', 1800),
    ('Laptop', 65000),
    ('Tablet', 25000)
)
SELECT name, price
FROM products
WHERE price BETWEEN 500 AND 2000
   OR price BETWEEN 20000 AND 30000;

BETWEEN with Dates

SQLite commonly stores dates as text in YYYY-MM-DD format. When dates use this ISO format, they can be compared correctly with BETWEEN.

WITH events(event_name, event_date) AS (
  VALUES
    ('Workshop', '2026-08-10'),
    ('Seminar', '2026-08-15'),
    ('Conference', '2026-08-20'),
    ('Meeting', '2026-08-25'),
    ('Webinar', '2026-08-30')
)
SELECT event_name, event_date
FROM events
WHERE event_date BETWEEN '2026-08-15' AND '2026-08-25'
ORDER BY event_date;

BETWEEN with Text Values

BETWEEN can also compare text values according to the database’s text sorting rules.

WITH names(name) AS (
  VALUES
    ('Aarav'),
    ('Bhavna'),
    ('Chetan'),
    ('Deepak'),
    ('Meera'),
    ('Rohan')
)
SELECT name
FROM names
WHERE name BETWEEN 'Bhavna' AND 'Meera'
ORDER BY name;

BETWEEN with ORDER BY

You can use ORDER BY to sort the rows returned by a BETWEEN condition.

WITH students(name, score) AS (
  VALUES
    ('Aman', 72),
    ('Neha', 91),
    ('Rahul', 65),
    ('Priya', 84),
    ('Karan', 78)
)
SELECT name, score
FROM students
WHERE score BETWEEN 70 AND 90
ORDER BY score DESC;

BETWEEN with LIMIT

In SQLite, BETWEEN can be combined with ORDER BY and LIMIT to return only a selected number of matching rows.

WITH products(name, price) AS (
  VALUES
    ('Mouse', 500),
    ('Keyboard', 800),
    ('Webcam', 1000),
    ('Headphones', 1400),
    ('Speaker', 1600)
)
SELECT name, price
FROM products
WHERE price BETWEEN 500 AND 1600
ORDER BY price DESC
LIMIT 3;

BETWEEN with Calculations

The expression being tested can also contain a calculation. Here, the total value is calculated before the range is checked.

WITH products(name, price, quantity) AS (
  VALUES
    ('Mouse', 500, 2),
    ('Keyboard', 800, 1),
    ('Monitor', 1500, 2),
    ('Webcam', 700, 3)
)
SELECT name, price, quantity,
       price * quantity AS total
FROM products
WHERE price * quantity BETWEEN 1000 AND 2000;

BETWEEN vs Comparison Operators

The following two queries produce the same range condition. BETWEEN is a shorter way to express a value greater than or equal to the lower boundary and less than or equal to the upper boundary.

WITH scores(student, score) AS (
  VALUES
    ('Aman', 55),
    ('Neha', 65),
    ('Rahul', 75),
    ('Priya', 85)
)
SELECT student, score
FROM scores
WHERE score >= 60
  AND score <= 80;

BETWEEN with Multiple Columns

You can use separate BETWEEN conditions to filter more than one column.

WITH employees(name, age, salary) AS (
  VALUES
    ('Aarav', 25, 50000),
    ('Meera', 30, 65000),
    ('Rohan', 35, 72000),
    ('Anika', 40, 85000),
    ('Vikram', 28, 60000)
)
SELECT name, age, salary
FROM employees
WHERE age BETWEEN 25 AND 35
  AND salary BETWEEN 50000 AND 75000;

Common BETWEEN Uses

Use	Example	Purpose
Numbers	price BETWEEN 500 AND 1500	Find values within a numeric range
Dates	date BETWEEN ‘2026-01-01’ AND ‘2026-12-31’	Find dates within a period
Text	name BETWEEN ‘A’ AND ‘M’	Filter according to text ordering
NOT BETWEEN	score NOT BETWEEN 40 AND 60	Find values outside a range

Advantages of BETWEEN

Makes range-based filtering easy to read.
Includes both the lower and upper boundary values.
Works with numeric values and ISO-formatted date strings in SQLite.
Can be combined with AND and OR conditions.
Can be used with calculations and expressions.

Best Practices

Remember that BETWEEN includes both boundary values.
Use NOT BETWEEN when values outside a range are required.
Use ISO YYYY-MM-DD date strings when comparing dates stored as text in SQLite.
Use parentheses when combining multiple range conditions with AND and OR.
Use IS NULL separately when handling NULL values.

The BETWEEN operator provides a simple way to filter values within a range. Because the boundaries are inclusive, it is especially useful for prices, scores, dates, salaries, quantities, and other range-based searches.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.