The BETWEEN Statement: SQL Statement Fundamentals

Hey there, folks! Welcome back to our journey in learning SQL. Our last few of posts covered the ORDER BY clause, LIMIT statement and the COUNT function. (See full list of SQL tutorials here.)

Today, we’re going to cover the BETWEEN statement. This is the start of some deeper material. In addition to the BETWEEN statement, we’ll also soon be covering IN and LIKE statements.

About the BETWEEN Statement

The BETWEEN statement (rather, operator) is used to match a value against a specified range of values. Maybe we want to get transactions between a certain dollar amount.

For example, value BETWEEN low AND high;

More about the BETWEEN statement. If the value is greater than or equal to the low value and  less than or equal to the high value, the expression returns true, or vice versa. Also, the BETWEEN operator can be rewritten by using the greater than or equal to ( >=) or less than or equal to ( <=) operators as the following statement.

value >= low and value <= high;

One other way to think about the BETWEEN statement is that takes two WHERE statements and lumps them into one. Think back to the WHERE statement, as we would say first:

SELECT column1 FROM table WHERE column1 >= 2 AND column1 <= 7;

To BETWEEN or NOT BETWEEN

Conversely, you can extend the usage of the original BETWEEN operator to NOT BETWEEN. This is similar in concept, except working toward exclusion, instead of inclusion. So, if we want to check if a given value is outside of a range, we can use the NOT BETWEEN operator as below.

value NOT BETWEEN low AND high;

Again, similarly to the double WHERE statements, NOT BETWEEN simplifies the burden into a single statement. For sanity, we’ll skip the SELECT WHERE example.

Using BETWEEN in pgAdmin / PostgreSQL

Below, we’ve taken the BETWEEN statement for a road test on our address table.

SELECT address_id,address FROM address

WHERE address_id BETWEEN 10 AND 20;

How sweet was that? Syonara, comparison operators. Conversely, if we convert the above query into a NOT BETWEEN operator, we should see the below statement return the following results.

SELECT address_id,address FROM address

WHERE address_id NOT BETWEEN 10 AND 20;

Okay, that was cool. Let’s hold up for a quick second. What about non-integer columns, such as data? Caveat: there’s a lot more to data types than our humble example below. For the purposes of this article, we can take a YYYY-MM-DD date and place the values into strings.

SELECT rental_id,rental_date,inventory_id FROM rental

WHERE rental_date BETWEEN ‘2005-05-24’ AND ‘2005-05-27’;

Voila!

Wrap-Up

Alright, for those of you reading this later, it’s 2 a.m. on a Friday night / Saturday morning, and I’m about ready to call it a night. Sorry I’m not providing a better conclusion for you today. I’m excited to delve further into more complex SQL queries. Keep track of my shared journey of a beginner learning SQL. Cheers!

Zach