10. Between and Not Between

The between operator does exactly what its name suggests: it tests whether one date or number is between two other dates/numbers. Let's say you've been asked to pull all gifts between Giving Tuesday 2023 (November 28, 2023) and the end of the year. We know from the last chapter that we could write this as:

select *
from transactions
where transaction_date >= '2023-11-28'
    and transaction_date <= '2023-12-31'

But, (extremely infomercial voice) there's gotta be a better way! There is, and it's the between operator. We can rewrite this query as:

select *
from transactions
where transaction_date between '2023-11-28' and '2023-12-31'

Note that between is inclusive, meaning that the query above will find all gifts between the two listed dates as well as gifts on the dates themselves. So a gift made on December 31, 2023 will be included in the results, as will a gift on November 28, 2023.

It works with numbers, too

Your mid-level team has asked that you find all donors with a gift of between $1,000 and $4,999.99. You can write this as:

select *
from transactions
where amount between 1000 and 4999.99

Exercise 1: Between

You need to generate a list of all transactions from the month of March 2021 so that acknowledgements can be sent. Write an SQL query to do that.

Exercise 2: Complex Between

Your major gifts team needs a list of all gifts of $25,000 to $50,000 from the year 2022. Write an SQL query to list these gifts.

Not Between

While not generally useful, there are cases where you will want to identify all records where some value is not between two specific values. For example, if you needed to generate a list of all transactions that are not mid-level gifts, you might run a query like this:

select *
from transactions
where amount not between 1000 and 4999.99

This construction is slightly awkward. It's not something you'll encounter a need for often, and most people don't speak and think in these terms. But I wanted to let you know that it's possible!

Exercise 3: Not between

You've been asked to generate a list of all gifts in 2023 that did not come in during the months of May and June. Write an SQL query to generate this list.

Between with Strings

between also works with letters and text. For example, we can check whether the letter f is between the letters d and x, or whether MI is between AR and NM. However, in practice you will very rarely use between in this way. I will not be covering this usage of between, but I wanted to make sure you knew it was possible.