Between in CockroachDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:


GeSHi Error: GeSHi could not find the language code (using path /nas/content/live/orkbprod/wp-content/plugins/codecolorer/lib/geshi/) (code 2)

Between SQL in Cockroach

1
2
3
SELECT txtNameProduct
FROM tblProducts
WHERE curPrice BETWEEN x_value AND y_value

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:

1
2
SELECT txtNameProduct, curPrice
FROM tblProducts

Which returns the following records:

txtNameProductcurPrice
1962 VW Bug with infinite mi$293
2007 Ford Mustang with 120,832 mi$3281
Tesla 3 – RWD – Mid Range$42000
Tesla 3 – AWD – Long Range$53000
Tesla 3 – AWD – Performance$64000
Tesla S – P75 AWD$81000
Tesla S – P90 AWD$102000
Tesla S – P100 AWD$129900

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?

1
2
3
SELECT txtNameProduct, curPrice
FROM tblProducts
WHERE curPrice BETWEEN 1500 AND 3000

Execution of the above SQL gives us the following:

txtNameProductcurPrice
2007 Ford Mustang with 120,832 mi$3281

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:

1
2
SELECT txtNameProduct, numMiles, curPrice
FROM tblProducts

Returns the following recordset:

txtNameProductnumMilescurPrice
1962 VW Bug400000$300
2006 Ford Mustang120832$3281
2011 Mazda 378000$9998
Tesla 3 – RWD – Mid Range0$42000
Tesla 3 – AWD – Long Range0$49000
Tesla 3 – AWD – Performance0$63000
Tesla S – P75 AWD0$75000
Tesla S – P90 AWD0$95000
Tesla S – P100 AWD0$129000

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.

1
2
3
4
SELECT txtNameProduct, numMiles, curPrice
FROM tblProducts
WHERE curPrice =< 3000
AND numMiles BETWEEN 0 AND 150000

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:

1
2
SELECT txtNameProduct, numSeats, numMiles, curPrice
FROM tblProducts
txtNameProductnumSeatsnumMilescurPrice
1962 VW Bug3.5420050$299
2006 Ford Mustang4120651$2891
2009 Mazda Miata2119030$7900
2010 Mazda 3579000$9700
2009 Mazda 3 wagon684000$9840
2009 Ford Mustang4109000$9888
Tesla 3 – RWD – Mid Range50$43000
Tesla 3 – AWD – Long Range50$51000
Tesla 3 – AWD – Performance50$62000
Tesla S – P75 AWD70$80000
Tesla S – P90 AWD70$100000
Tesla S – P100 AWD70$135000

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:

1
2
3
4
5
SELECT txtNameProduct, n_mpg, numMiles, curPrice
FROM tblProducts
WHERE curPrice =< 11000
AND numMiles =< 199000
AND numSeats BETWEEN 3 AND 5

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:

txtNameProductnumSeatsnumMilescurPrice
2006 Ford Mustang4120651$2891
2009 Mazda Miata2119030$7900
2010 Mazda 3579000$9700
2009 Ford Mustang4109000$9888

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:

1
2
SELECT txtNameStudent, numAge
FROM tblUsers
txtNameStudentnumAge
Georgio3.5
Veronika4
Jimmie4.5
Tonica5
Jameson5.5
Kimmie6
Toby7

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

1
2
3
SELECT txtNameStudent, numAge
FROM tblUsers
WHERE numAge BETWEEN 4 AND 5
txtNameStudentnumAge
Veronika4
Jimmie4.5
Tonica5

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?

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.