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:

SELECT t_name_product
FROM Products
WHERE c_price BETWEEN x AND y

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:

SELECT t_name_product, c_price
FROM Products

The query returns the following records:

t_name_productc_price
1960 VW Beetle with infinite miles$300
2007 Ford Focus with 130,892 miles$2991
Tesla Model 3 – RWD – Mid Range$43000
Tesla Model 3 – AWD – Long Range$51000
Tesla Model 3 – AWD – Performance$62000
Tesla Model S – P75 AWD$80000
Tesla Model S – P90 AWD$100000
Tesla Model S – P100 AWD$135000

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:

SELECT t_name_product, c_price
FROM Products
WHERE c_price BETWEEN 2500 AND 3000

Executing this query will give us the following results:

t_name_productc_price
2007 Ford Focus with 130,892 miles$2991

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:

SELECT t_name_product, n_miles, c_price
FROM Products

We’ll get results like those shown below:

t_name_productn_milesc_price
1960 VW Beetle400000$300
2007 Ford Focus130892$2991
2010 Mazda 378000$9998
Tesla Model 3 – RWD – Mid Range0$43000
Tesla Model 3 – AWD – Long Range0$51000
Tesla Model 3 – AWD – Performance0$62000
Tesla Model S – P75 AWD0$80000
Tesla Model S – P90 AWD0$100000
Tesla Model S – P100 AWD0$135000

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.

SELECT t_name_product, n_miles, c_price
FROM Products
WHERE c_price =< 3000
AND n_miles BETWEEN 0 AND 150000

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:

SELECT t_name_product, n_seats, n_miles, c_price
FROM Products
t_name_productn_seatsn_milesc_price
1960 VW Beetle3.5400000$300
2007 Ford Focus4130892$2991
2008 Mazda Miata2120000$8000
2010 Mazda 3580000$9800
2009 Mazda 3 wagon685000$9850
2009 Ford Mustang4120000$9999
Tesla Model 3 – RWD – Mid Range50$43000
Tesla Model 3 – AWD – Long Range50$51000
Tesla Model 3 – AWD – Performance50$62000
Tesla Model S – P75 AWD70$80000
Tesla Model S – P90 AWD70$100000
Tesla Model S – P100 AWD70$135000

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:

SELECT t_name_product, n_mpg, n_miles, c_price
FROM Products
WHERE c_price =< 10000
AND n_miles =< 150000
AND n_seata BETWEEN 4 AND 5

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:

t_name_productn_seatsn_milesc_price
2007 Ford Focus4130892$2991
2008 Mazda Miata2120000$8000
2010 Mazda 3580000$9800
2009 Ford Mustang4120000$9999

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:

SELECT t_name_student, n_age
FROM Users
t_name_studentn_age
Georgie3.5
Veronica4
Timmie4.5
Monica5
James5.5
Kim6

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

SELECT t_name_student, n_age
FROM Users
WHERE n_age BETWEEN 4 AND 5
t_name_studentn_age
Veronica4
Timmie4.5
Monica5

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.