Navicat Blog

May 29, 2019 by Robert Gravelle

The Some SELECT Queries You Must Know blog presented a couple of the most important queries to know, along with some examples. Continuing with that theme, today's blog focuses on the invaluable BETWEEN operator.

Limiting Values to a Certain Range

One way to filter the number of rows returned from a query is to limit the values of one or more fields to those that fall within a range. Typically, this can be done using the >= and <= operators. To illustrate, here's a query that returns information about Sakila film rentals that occurred between the 5th and 6th of July of 2005:

SELECT
        customer_list.`name`,
        rental.rental_date,
        film.title
FROM
        customer_list
        INNER JOIN rental ON customer_list.ID = rental.customer_id
        INNER JOIN film ON rental.inventory_id = film.film_id
WHERE
        rental_date >= '2005-07-05' AND rental_date <= '2005-07-06'

A shorter and more readable way to delineate the same range is to use the BETWEEN operator. The BETWEEN operator is used to select the value within a certain range. The values defined as part of the BETWEEN range are inclusive i.e. the values that are mentioned in the range are included at the start and end values:

WHERE rental_date BETWEEN '2005-07-05' AND '2005-07-06'

In both cases, the results are constrained to the given date range:

name rental_date title
----------------------------------------------------------------
JAIME NETTLES 2005-07-05 22:49:24 TEQUILA PAST
PAMELA BAKER 2005-07-05 22:56:33 STAR OPERATION
EDUARDO HIATT 2005-07-05 22:59:53 BRIDE INTRIGUE
FERNANDO CHURCHILL 2005-07-05 23:13:51 BLADE POLISH
CARMEN OWENS 2005-07-05 23:25:54 CANDLES GRAPES
JOE GILLILAND 2005-07-05 23:32:49 TOURIST PELICAN
APRIL BURNS 2005-07-05 23:44:37 WIZARD COLDBLOODED
ERICA MATTHEWS 2005-07-05 23:46:19 JACKET FRISCO

While ideal for dates, the BETWEEN operator works equally well with other data types. Consider this further filtering of the above data that limits the results to those rentals that cost between 2.99 and 4.99:

SELECT
        customer_list.`name`,
        rental.rental_date,
        film.title,
        film.rental_rate
FROM
        customer_list
        INNER JOIN rental ON customer_list.ID = rental.customer_id
        INNER JOIN film ON rental.inventory_id = film.film_id
WHERE
        rental.rental_date BETWEEN '2005-07-05' AND '2005-07-06'
        AND film.rental_rate BETWEEN 2.99 AND 4.99

name rental_date title rental_rate
----------------------------------------------------------------------------------
JAIME NETTLES 2005-07-05 22:49:24 TEQUILA PAST 4.99
PAMELA BAKER 2005-07-05 22:56:33 STAR OPERATION 2.99
CARMEN OWENS 2005-07-05 23:25:54 CANDLES GRAPES 4.99
JOE GILLILAND 2005-07-05 23:32:49 TOURIST PELICAN 4.99
APRIL BURNS 2005-07-05 23:44:37 WIZARD COLDBLOODED 4.99
ERICA MATTHEWS 2005-07-05 23:46:19 JACKET FRISCO 2.99

Conclusion

Today's blog presented the all-important BETWEEN operator, along with some examples using Navicat Premium as the database client. Navicat helps you code fast with Code Completion and customizable Code Snippets by getting suggestions for keywords and stripping the repetition from coding. You can try it for 14 days completely free of charge for evaluation purposes.

Navicat Blogs
Feed Entries
Blog Archives
Share