Between in CockroachDB

Introduction

In this lesson document, we will explain the use of an operator called Between in CockroachDB using SQL, including but not limited to:

• What? What does the Between operator accomplish?
• Why? In what situations will we benefit from using the “between” operator?
• How? How do we use the “BETWEEN” operator in the SQL we write for Cockroach?

Prerequisites

• Beginner understanding of how to run the most basic of SQL statements against your CockroachDB database, using Dbeaver or some other database administration tool, or by using code like Python or .Net in your database application that provides a way to connect to your Cockroach data and query that data with SQL.
• Beginner level understanding of how the “WHERE” and “AND” clauses work in SQL statements.

What is “BETWEEN”?

In CockroachDB, BETWEEN is often used as part of a WHERE clause in SQL; returning a true or false, depending on whether the values you use with the “BETWEEN” operator. We use this operator in order to limit or filter a recordset to return rows that meet values found between x and y, as seen here:

Between Syntax

Now let’s back up and take a look at syntax:

Between SQL in Cockroach

Curious about inclusion of values before and after the “between” operator? Keep reading.

Use cases for “BETWEEN”

For this exercise, our goal is to select a car that fits our budget (and other) constraints from a table called “Products”. First, let us look at all the records in our “Products” table by executing the following SQL on our Cockroach database:

Which returns the following records:

Now we add a “WHERE” clause using the “BETWEEN” operator to modify our SELECT statement. We want to filter the rows returned to show only the automobiles within our budget range. The maximum price we want to pay is $4,000 but we don’t want to buy a piece of junk. Can you guess what the new line of SQL will look like?

Execution of the above SQL gives us the following:

Do you understand why the results dropped from eight automobiles returned down to one automobile returned?

At this stage, if you are looking at your Cockroach database and saying to yourself, “When I don’t want to measure purely by price because miles are important.” So let us put more data in our table and add a field (column) called “numMiles” to keep track of how many miles each car has on it.

Our new table with all records looks like:

Returns the following recordset:

Now we can filter the “tblProducts” table by the “numMiles” AND “curPrice” columns to retrieve only the autos within our budget range AND that have the number of miles we want to stay under.

Some items of interest regarding the SQL command above:

• We changed the filtering of “curPrice” to use “=<” operator instead of “BETWEEN” because we don’t have a lower limit on the price we are looking for when shopping.
• We are using “BETWEEN” to filter our recordset to show us rows where miles are between 0 and 150,000 but this is not really necessary. We could have also used “=<” here, too.

So. Let us fix this by exposing more of our database structure:

Now let’s assume we changed our purchase constraints to the following:

• A budget of $11,000.
• We want an automobile with less than 199,000 miles.
• It is important that the auto is a specific size in terms of the seating we want. We are searching for a back seat, which disqualifies 2-seat automobiles like the Mazda Miata. We don’t want a stationwagon, bus, cargo helicopter, or semi rig, so 5 is our seat upper limitation. Given those requirements, the Tesla S, though not being a wagon, does allow for two extra smaller seats in the furthest rear area so it is disqualified from this search. That said, Teslas are far above our price range. We’re making the assumption that these cars are limited to no more than two people in the front seats and three in the back seats.

We’ll re-write the SQL statement to reflect the above-stated needs:

This brings up an important question: Is the “BETWEEN” operator inclusive? In other words, does “BETWEEN 4 and 7” (we will think in terms here of only integers, to simplify) give us only 5 and 6 OR does it give us 4, 5, 6, and 7? Yes. It is inclusive in that way where “BETWEEN 4 and 5” returns 4 and 5. Let us see what our new SQL execution returns:

Another use case for “BETWEEN”

The situation: We work in a kindergarten class and want to get a list of students who are equal to or older than 5 while being younger than or equal to 6. So yeah, 5 and 6 year olds. To begin, here’s a full table of students of all ages:

When we add our “BETWEEN” clause, we get:

Notice how Georgio did not appear in our new returned recordset because he is 3.5 in age, which is below our lower limit of age four. Also, rows for Jameson and Kimmie were not returned because their ages are above our upper limit of five.

Conclusion

Here in this document we gained an understanding of how and when to use the “BETWEEN” operator in CockroachDB SQL. We also explored two use cases where a person may want to utilize the “between” SQL operator. What are your thoughts on using “BETWEEN” to filter specific records in your Cockroach database queries?