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:

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:

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.