Using the Postgres BETWEEN SQL Operator

Introduction

When you query a database, there are times when you might want to limit your results to a certain range of values. In PostgreSQL, it’s easy to accomplish this task with the help of the BETWEEN operator. This article will explain the advantages of this operator and provide examples of using Postgres BETWEEN in SQL queries.

Prerequisites

In order to follow along with the examples shown in this article, you’ll need some basic knowledge of how to execute SQL statements in PostgreSQL. You can use a database administration tool like PG Admin, or you can choose to connect to the database through a Python script or some other type of code. It will also be helpful to have some knowledge of how WHERE and AND clauses work in database queries.

What is the BETWEEN operator?

In PostgreSQL, BETWEEN is typically used as part of a WHERE clause in a SQL query. It returns a true or false value, depending on the values you use with the BETWEEN operator. We use this operator to limit or filter a set of records in order to return rows that match values found between x and y. An example of a query that uses Postgres BETWEEN in SQL is shown below:

A comprehensive use case for the PostgreSQL BETWEEN operator

Let’s look at a real-life example of a query that uses the BETWEEN operator. In this example, our goal will be to select a car that fits our budget and other constraints. We’ll be selecting the car from a table called Products. We’ll get started by looking at all the records in our Products table, using the following query:

The query returns the following records:

Now let’s modify our query a bit. We’ll insert a WHERE clause that includes the BETWEEN operator to our SELECT statement. The goal is to limit the record set to car(s) within a certain price range. Our budget max is $3,000, but we’re not looking to buy a $500 car, so we want to restrict our results. The BETWEEN operator will let us specify both the upper and lower bounds for the query, allowing us to narrow down the record set to our target price range:

Executing this query will give us the following results:

Notice that our results were reduced from eight rows down to just one row. That’s the PostgreSQL BETWEEN operator at work.

At this point, we’ve found a car within our specified price range, but we’re having second thoughts about our original query criteria. We don’t want to select a car based on price alone– mileage is important as well. Let’s put some more data in our table, adding a column called n_miles to represent how many miles each car has on it.

We can query our updated table with its new column:

We’ll get results like those shown below:

Now we can filter our products table by both the n_miles and c_price fields to find the car(s) that fit our price range and have a certain number of miles.

There are a couple things to take note of in the SQL shown above:

• We changed our filtering of “c_price” to use the “=<” operator instead of “BETWEEN”. This is because we don’t have a lower limit on our price range, now that we’re also taking mileage into consideration
• We’re now using “BETWEEN” to filter our record set and show us rows where the mileage is between 0 and 150,000. However, the BETWEEN operator seems a bit unnecessary here– can’t we just use the “=<” operator instead?

Let’s make some changes. First, we’ll review our data again, using this query:

Then, we’ll revise our criteria a bit. Let’s say our purchase constraints now include the following:

• Our budget is $10,000.
• We want a car with fewer than 200,000 miles.
• We’re looking for cars of a certain size in terms of seating. We need a back seat, which eliminates two-seater cars. We don’t want a wagon, so five seats will be our upper limit. We’re assuming that for most cars, you can seat two people in the front and three in the back seat.

Let’s rewrite our SQL statement to reflect these needs:

This revised query brings up an important question: Is the “BETWEEN” operator inclusive? In other words, does the clause “BETWEEN 4 and 7” return only 5 and 6 OR does it give us 4, 5, 6, and 7? The answer is: Yes, it’s inclusive. This means that, in our example, “BETWEEN 4 and 5” returns 4 and 5. Let’s see what our query returns with our new criteria in place:

One more use case for learning to use “BETWEEN”

Let’s look at a different scenario where it can be helpful to use the BETWEEN operator in PostgreSQL. In this example, let’s imagine that we work at a daycare and want to get a list of students who are at least four years old, but also no more than five years old. We’ll start by retrieving a full list of students of all ages:

When we add our “BETWEEN” clause, our query will look like this:

Notice that Georgie was left out of our filtered record set because he is 3.5 years old, which falls below our lower threshold of four. James and Kim were also left out because their ages are above our upper threshold of five.

Conclusion

Being able to filter a record set to include only a certain range of values can help you get more relevant results from your database queries. In this tutorial, we explained how to use the Postgres BETWEEN operator in SQL statements. We also explored two real-world situations where it would make sense to use this operator in a query. With these examples to guide you, you’ll have no trouble using the BETWEEN operator to filter and limit the results of your own database queries.