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:
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:
txtNameProduct | curPrice |
---|---|
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:
txtNameProduct | curPrice |
---|---|
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:
txtNameProduct | numMiles | curPrice |
---|---|---|
1962 VW Bug | 400000 | $300 |
2006 Ford Mustang | 120832 | $3281 |
2011 Mazda 3 | 78000 | $9998 |
Tesla 3 – RWD – Mid Range | 0 | $42000 |
Tesla 3 – AWD – Long Range | 0 | $49000 |
Tesla 3 – AWD – Performance | 0 | $63000 |
Tesla S – P75 AWD | 0 | $75000 |
Tesla S – P90 AWD | 0 | $95000 |
Tesla S – P100 AWD | 0 | $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 |
txtNameProduct | numSeats | numMiles | curPrice |
---|---|---|---|
1962 VW Bug | 3.5 | 420050 | $299 |
2006 Ford Mustang | 4 | 120651 | $2891 |
2009 Mazda Miata | 2 | 119030 | $7900 |
2010 Mazda 3 | 5 | 79000 | $9700 |
2009 Mazda 3 wagon | 6 | 84000 | $9840 |
2009 Ford Mustang | 4 | 109000 | $9888 |
Tesla 3 – RWD – Mid Range | 5 | 0 | $43000 |
Tesla 3 – AWD – Long Range | 5 | 0 | $51000 |
Tesla 3 – AWD – Performance | 5 | 0 | $62000 |
Tesla S – P75 AWD | 7 | 0 | $80000 |
Tesla S – P90 AWD | 7 | 0 | $100000 |
Tesla S – P100 AWD | 7 | 0 | $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:
txtNameProduct | numSeats | numMiles | curPrice |
---|---|---|---|
2006 Ford Mustang | 4 | 120651 | $2891 |
2009 Mazda Miata | 2 | 119030 | $7900 |
2010 Mazda 3 | 5 | 79000 | $9700 |
2009 Ford Mustang | 4 | 109000 | $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 |
txtNameStudent | numAge |
---|---|
Georgio | 3.5 |
Veronika | 4 |
Jimmie | 4.5 |
Tonica | 5 |
Jameson | 5.5 |
Kimmie | 6 |
Toby | 7 |
When we add our “BETWEEN” clause, we get:
1 2 3 | SELECT txtNameStudent, numAge FROM tblUsers WHERE numAge BETWEEN 4 AND 5 |
txtNameStudent | numAge |
---|---|
Veronika | 4 |
Jimmie | 4.5 |
Tonica | 5 |
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