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:
sql
column_name BETWEEN lower_bound AND upper_bound
For example:
sql
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:
sql
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:
sql
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:
sql
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:
sql
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:
sql
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
sql
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
sql
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
sql
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:
sql
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:
sql
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:
sql
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():
sql
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:
sql
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:
sql
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:
sql
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:
sql
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:
sql
column >= low AND column <= high
But SQL BETWEEN shortens this to:
sql
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:
sql
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:
sql
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.