If you’ve spent even a little time working with databases, you’ve probably run into the SQL BETWEEN operator. It’s one of those simple tools that makes sorting and filtering through rows much easier. Instead of writing complicated conditions, you can grab a range of data with just a few words. And the best part? It works with numbers, dates, and even text values. Let’s take a closer look at how you can make the most out of BETWEEN in SQL.

7 Common Ways to Use SQL BETWEEN Operator

Filtering Numerical Ranges

The most obvious use of BETWEEN is with numbers. Say you have a table of product prices, and you want to find everything that costs between $10 and $50. Instead of writing a long set of conditions like price >= 10 AND price <= 50, you just write:

sql

CopyEdit

SELECT * FROM products

WHERE price BETWEEN 10 AND 50;

That is it. The database will read it the same way but with less typing. It also makes your query nicer to read when you need to read it again later. If you're doing budget numbers, discounts, or other number data, BETWEEN is a convenient shortcut that keeps your code clean.

Working with Date and Time Values

If you thought BETWEEN was only for numbers, you’re in for a treat. It’s just as helpful when you’re handling dates and times. For example, if you have a table of customer orders and you want to see all orders placed between January 1st and March 31st, your query would look something like this:

sql

CopyEdit

SELECT * FROM orders

WHERE order_date BETWEEN '2025-01-01' AND '2025-03-31';

You don't have to break it into two comparisons. BETWEEN covers the start and end dates, inclusive. This is especially useful when running reports for a specific quarter, fiscal year, or promotional period. Just remember that both endpoints you list are part of the result unless you specifically code it otherwise.

Selecting Text Values Alphabetically

Here’s something you might not expect — BETWEEN can also work with text. SQL compares strings alphabetically, letter by letter. So, if you wanted a list of customers whose names fall between "Anderson" and "Parker," you could do:

sql

CopyEdit

SELECT * FROM customers

WHERE last_name BETWEEN 'Anderson' AND 'Parker';

This trick is useful when grouping records by ranges of names or codes. It also comes in handy when you're cleaning up data sets, especially if you need to split work between teams alphabetically. Just keep in mind that string comparisons are case-sensitive in some systems, so it’s smart to double-check how your database handles uppercase and lowercase letters.

Combining BETWEEN with NOT for Exclusions

Sometimes you want everything except what falls inside a range. That’s where NOT BETWEEN steps in. It flips the filter. For example, if you want to find products priced either below $10 or above $50 — excluding the $10 to $50 range — you would write:

sql

CopyEdit

SELECT * FROM products

WHERE price NOT BETWEEN 10 AND 50;

This saves you from creating complicated OR conditions. And again, it makes your queries easier to read later on. When you revisit an old project or hand your code off to someone else, you’ll be glad you kept it simple with NOT BETWEEN.

Using BETWEEN in JOIN Conditions

Here’s a trick that’s not talked about enough. You can actually use BETWEEN when joining tables, too. Sometimes, you have a main table and a lookup table where the matches fall within a certain range. For instance, if you are matching transactions to tax brackets based on income ranges:

sql

CopyEdit

SELECT t.transaction_id, b.tax_rate

FROM transactions t

JOIN tax_brackets b

ON t.amount BETWEEN b.min_income AND b.max_income;

Instead of matching only exact values, you’re matching where a value fits between two limits. It opens up a lot of possibilities, especially in financial and reporting systems where ranges are a key part of the logic.

Handling NULLs with Caution

One thing you have to watch out for: BETWEEN doesn’t deal with NULL values the way you might expect. If either side of the comparison has a NULL, the result will be unknown — and SQL will quietly skip it. For example, if a table has a price listed as NULL, it won’t be picked up by:

sql

CopyEdit

SELECT * FROM products

WHERE price BETWEEN 10 AND 50;

It won’t trigger an error, but you also won't see that row in your results. If you want to make sure you’re not missing out on anything important, it’s smart to add a specific check for NULL values when needed:

sql

CopyEdit

SELECT * FROM products

WHERE price BETWEEN 10 AND 50

OR price IS NULL;

That way, you don't unintentionally lose data just because a field is empty.

Using BETWEEN with Calculated Columns or Expressions.

Here’s how it works:

You're not limited to using BETWEEN only with simple column names. You can also use it with calculated expressions right inside your query.

For example, suppose you want to filter orders where the total price (quantity × unit price) falls between $100 and $500. You can write:

sql

CopyEdit

SELECT * FROM orders

WHERE (quantity * unit_price) BETWEEN 100 AND 500;

Instead of pre-calculating a new column or creating a view, you just do the math right inside the WHERE clause.

This is really helpful when you need a quick filter based on a formula without changing your actual table design.

Final Thoughts

SQL’s BETWEEN operator is like a secret weapon for simple, clear queries. It trims down your conditions, saves you time, and helps your queries stay readable — whether you're filtering numbers, dates, or even text. Plus, with a few extra tricks like using NOT BETWEEN or combining it with joins, you can handle all kinds of real-world data challenges without making things harder than they need to be. If you haven’t made friends with BETWEEN yet, now’s a good time to start. It’s small, but it makes a big difference when you're building smarter SQL queries.