SQL BETWEEN: Syntax, Usage, and Examples

The BETWEEN operator in SQL lets you filter data by checking whether a value falls within a specified range. You can use it with numbers, dates, or even text, and it makes queries cleaner than using multiple AND conditions.

How to Use SQL BETWEEN

The basic syntax looks like this:

column_name BETWEEN lower_bound AND upper_bound

For example:

SELECT * FROM products
WHERE price BETWEEN 10 AND 50;

This returns all products with a price greater than or equal to 10 and less than or equal to 50. The BETWEEN SQL syntax is inclusive, so both the lower and upper bounds are part of the result.

If you want the opposite—values outside the range—use NOT BETWEEN:

SELECT * FROM products
WHERE price NOT BETWEEN 10 AND 50;

When to Use the SQL BETWEEN Operator

Filter Numeric Ranges

One of the most common uses of SQL BETWEEN is filtering numbers:

SELECT * FROM employees
WHERE salary BETWEEN 40000 AND 60000;

This is simpler than writing salary >= 40000 AND salary <= 60000, especially when the range is reused or dynamically generated.

Filter Dates With SQL Query for In Between Dates

Working with date fields? You can quickly filter a range:

SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

You’ll get all orders placed from January 1st through March 31st. That’s a SQL query for in between dates in its simplest form.

Filter Alphabetically

Use SQL BETWEEN with text values to retrieve rows within an alphabetical range:

SELECT * FROM customers
WHERE last_name BETWEEN 'A' AND 'M';

This returns all customers whose last names start with letters from A to M, inclusive.

Combine BETWEEN With Other Conditions

You can pair SQL BETWEEN with AND, OR, or other SQL operators for more precise filtering:

SELECT * FROM inventory
WHERE category = 'Electronics'
AND quantity BETWEEN 10 AND 100;

Here, you're narrowing results to electronics with stock in a particular range.

Examples of SQL BETWEEN in Action

Example 1: Using BETWEEN With Numbers

SELECT product_name, price
FROM products
WHERE price BETWEEN 25 AND 75;

You filter for products priced from $25 to $75.

Example 2: Using BETWEEN With Dates

SELECT event_name, event_date
FROM events
WHERE event_date BETWEEN '2025-04-01' AND '2025-04-30';

You retrieve all events scheduled in April 2025. This kind of SQL query for in between dates is common in dashboards and reports.

Example 3: Text Filtering With BETWEEN

SELECT student_name
FROM students
WHERE last_name BETWEEN 'D' AND 'L';

You fetch student names sorted by last names starting between 'D' and 'L'. This works alphabetically, based on the database's collation.

Learn More About SQL BETWEEN

SQL BETWEEN Is Inclusive

One key detail: SQL BETWEEN includes both the lower and upper limits. If you're filtering BETWEEN 10 AND 20, you'll include both 10 and 20. You don’t need to write extra logic to include boundary values.

Using SQL BETWEEN With NULL

BETWEEN doesn’t work if your target value is NULL. A NULL value represents an unknown, and comparing anything to NULL with BETWEEN returns false.

This query will not return rows where the column is NULL:

SELECT * FROM employees
WHERE bonus BETWEEN 100 AND 500;

If some bonuses are NULL, they’re excluded by default. Use IS NULL explicitly if needed:

SELECT * FROM employees
WHERE bonus BETWEEN 100 AND 500
OR bonus IS NULL;

BETWEEN With Expressions

You can use expressions or functions inside a BETWEEN clause:

SELECT * FROM orders
WHERE total_price BETWEEN subtotal * 0.9 AND subtotal * 1.1;

This filters rows based on a price within 10% of the subtotal.

Or with functions like NOW():

SELECT * FROM logins
WHERE login_date BETWEEN NOW() - INTERVAL 7 DAY AND NOW();

This pulls logins from the past 7 days.

SQL WHEN BETWEEN in CASE Expressions

BETWEEN can also appear in CASE expressions. For example:

SELECT name,
  CASE
    WHEN score BETWEEN 90 AND 100 THEN 'A'
    WHEN score BETWEEN 80 AND 89 THEN 'B'
    ELSE 'Needs Improvement'
  END AS grade
FROM assessments;

Here, you classify student grades using the SQL case expression and SQL BETWEEN together.

BETWEEN and Time Fields

If your table includes a DATETIME or TIMESTAMP field, make sure you consider time values too. For example:

SELECT * FROM appointments
WHERE appointment_time BETWEEN '2025-04-10 08:00:00' AND '2025-04-10 17:00:00';

This pulls all appointments for a specific day during business hours.

You can also extract just the date part if needed:

SELECT * FROM appointments
WHERE DATE(appointment_time) BETWEEN '2025-04-01' AND '2025-04-30';

SQL Query for In Between Dates and Times With Parameters

In dynamic SQL, you might use parameters like this:

SELECT * FROM bookings
WHERE booking_date BETWEEN :start_date AND :end_date;

This lets your application pass user-selected dates to filter data.

Avoid Overusing SQL BETWEEN With Text

Alphabetical filtering can be unpredictable depending on collation settings. For example, BETWEEN 'a' AND 'z' might not behave the same in all databases. Use with caution when dealing with non-ASCII characters or mixed casing.

Comparing SQL BETWEEN vs Multiple AND Conditions

You can always write:

column >= low AND column <= high

But SQL BETWEEN shortens this to:

column BETWEEN low AND high

Both produce the same results. The SQL BETWEEN version is easier to read and often preferred in reporting queries.

SQL BETWEEN With JOINs or Subqueries

You can use SQL BETWEEN in more complex joins:

SELECT *
FROM sales s
JOIN targets t
ON s.region_id = t.region_id
WHERE s.revenue BETWEEN t.target_min AND t.target_max;

This joins sales to targets and filters rows where sales hit the target range.

Or in subqueries:

SELECT *
FROM inventory
WHERE quantity BETWEEN (
  SELECT MIN(required_stock) FROM restock_levels
) AND (
  SELECT MAX(required_stock) FROM restock_levels
);

This pulls inventory items that are within restocking thresholds.

The SQL BETWEEN operator helps you write clear, powerful queries for filtering numeric, date, or text values inside a range. It simplifies logic that would otherwise require chained AND conditions and makes your code easier to follow. Whether you’re querying for records within a specific timeframe, grouping data into intervals, or creating user-friendly filters in reports, SQL BETWEEN saves time and improves readability.