PHP Developer News

Range-based Filtering With the BETWEEN Operator in MySQL

This post covers filtering in the WHERE clause conditional using the MySQL BETWEEN operator. Continue reading this excerpt of premium MySQL content I’m creating for anyone to learn how to use MySQL…







The Newsletter for PHP and MySQL Developers



Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.










Suggested Reading



Visit any of these MySQL beginner-friendly articles for additional free learning resources:



SELECT clause queries – MySQL Beginner Basics Series.Limit Rows with the WHERE clause – MySQL Beginner SeriesMySQL BETWEEN Operator Overview



For range type condition filters, we can use the BETWEEN operator. A BETWEEN search filter query allows you to specify upper and lower bounds – or a limits range – that a value should fall between, in order to be included in the returned results set.



BETWEEN operator queries are inclusive, meaning any returned query results do include both the specified upper and lower bounds (or limits) of the specified range. If that concept is confusing, worry not, as we will see example queries to dispel any doubt.



MySQL BETWEEN Operator Syntax



The syntax for range-based filter queries using BETWEEN consists of the BETWEEN keyword in addition to both range bounds separated by the AND keyword:



BETWEEN lower_limit AND upper_limit



BETWEEN operator queries can test ranges for numeric, string, and temporal (date and time) data.



Get your brand, product, or service the attention it needs with affordable classified ad placement in the OpenLampTech newsletter. Thank you for your support!



MySQL BETWEEN Operator Queries – Numeric Ranges



In this example query using the Sakila database ‘category’ table, we use the BETWEEN operator to look at just those rows with a ‘category_id’ column value between 1 and 5, including both of the bounds range limits:



mysql> SELECT category_id, name -> FROM category -> WHERE category_id BETWEEN 1 AND 5;



+-------------+-----------+| category_id | name |+-------------+-----------+| 1 | Action || 2 | Animation || 3 | Children || 4 | Classics || 5 | Comedy |+-------------+-----------+5 rows in set (0.06 sec)



Here is an equivalent query using the less than or equals to and greater than or equals to conditional operators:



mysql> SELECT category_id, name -> FROM category -> WHERE category_id >= 1 AND category_id <= 5;



+-------------+-----------+| category_id | name |+-------------+-----------+| 1 | Action || 2 | Animation || 3 | Children || 4 | Classics || 5 | Comedy |+-------------+-----------+5 rows in set (0.04 sec)



MySQL BETWEEN Operator Queries – Lower and Upper Limits Placement



Notice we should always place the smaller filter requirement (or lower bounds) first when using BETWEEN:



mysql> SELECT category_id, name -> FROM category -> WHERE category_id BETWEEN 5 AND 1;Empty set (0.00 sec)



This query returns no rows because it is not possible for a ‘category_id’ value to be both greater than or equal to 5 and less than or equal to 1.



MySQL BETWEEN Operator Queries – Date Ranges



Not only can you use the BETWEEN operator with numeric data, but you can also filter date ranges. For this example, I’m using a ‘friends’ table having this data:



mysql> SELECT first_name, birthday -> FROM friends;+------------+------------+| first_name | birthday |+------------+------------+| Max | 1975-01-23 || Mary | 1978-03-23 || Charlie | 1971-08-22 || Humpty | 1971-11-22 || Roger | 1975-08-22 || Jim | 1975-05-05 || Jupyter | 1978-07-22 |+------------+------------+7 rows in set (0.00 sec)



A date range BETWEEN filter is relatively straightforward. Be sure the older date is specified as the lower bounds and the most current or recent date is placed as the upper bounds.



If I wish to retrieve all rows from the ‘friends’ table where the ‘birthday’ column value is in the range of May 5th, 1975 and September 1st, 1975, I can use BETWEEN with those specific dates as shown in the following query:



mysql> SELECT first_name, birthday -> FROM friends -> WHERE birthday BETWEEN '1975-05-01' AND '1975-09-01';+------------+------------+| first_name | birthday |+------------+------------+| Roger | 1975-08-22 || Jim | 1975-05-05 |+------------+------------+2 rows in set (0.06 sec)



MySQL BETWEEN Operator Queries – String and Text Ranges



String and text data BETWEEN operator queries require a bit of thought. Because this type of BETWEEN filter on text data depends on the MySQL character set and collation settings, some results may be different.



On my MySQL development learning environment, the character set and collation are set to these values:



mysql> SELECT @@character_set_database AS char_set, @@collation_database AS collat;+----------+--------------------+| char_set | collat |+----------+--------------------+| utf8mb4 | utf8mb4_0900_ai_ci |+----------+--------------------+1 row in set (0.00 sec)



I’ll execute some simple BETWEEN operator queries on this sample data present in the ‘friends’ table:



mysql> SELECT first_name, last_name -> FROM friends;+------------+-----------+| first_name | last_name |+------------+-----------+| Max | Maxer || Mary | Murphy || Charlie | Charles || Humpty | Dumpty || Roger | Dodger || Jim | Russ || Jupyter | Moonbeam |+------------+-----------+7 rows in set (0.00 sec)



To find any rows with a ‘last_name’ column value that is between ‘M’ and ‘Z’, I use this query:



mysql> SELECT first_name, last_name -> FROM friends -> WHERE last_name BETWEEN 'M' AND 'Z';+------------+-----------+| first_name | last_name |+------------+-----------+| Max | Maxer || Mary | Murphy || Jim | Russ || Jupyter | Moonbeam |+------------+-----------+4 rows in set (0.00 sec)



This query filters for any rows where the ‘last_name’ column value is between ‘A’ and ‘D’:



mysql> SELECT first_name, last_name -> FROM friends -> WHERE last_name BETWEEN 'A' AND 'D';+------------+-----------+| first_name | last_name |+------------+-----------+| Charlie | Charles |+------------+-----------+1 row in set (0.00 sec)



Notice no rows are returned in this query because there are no ‘last_name’ column values that fall between ‘A’ and ‘C’:



mysql> SELECT first_name, last_name -> FROM friends -> WHERE last_name BETWEEN 'A' AND 'C';Empty set (0.00 sec)



Again, keep in mind that character set and collation play an important role in BETWEEN range queries where text and string data are concerned.



MySQL BETWEEN Operator Queries – Negate Ranges using the NOT keyword



You can negate the bounds of BETWEEN queries by using the NOT keyword with BETWEEN:



mysql> SELECT category_id, name -> FROM category -> WHERE category_id NOT BETWEEN 1 AND 5;+-------------+-------------+| category_id | name |+-------------+-------------+| 6 | Documentary || 7 | Drama || 8 | Family || 9 | Foreign || 10 | Games || 11 | Horror || 12 | Music || 13 | New || 14 | Sci-Fi || 15 | Sports || 16 | Travel |+-------------+-------------+11 rows in set (0.00 sec)



By using NOT BETWEEN 1 AND 5 as the filter condition, all rows having a ‘category_id’ value for anything other than 1 to 5 (including those bounds) are returned in the query results.



Discover premium articles, in-depth guides, instructional videos, and much more by joining the “MySQL Learning Tier” membership. You have access to exclusive content unpublished anywhere else with this membership. With new content being added regularly, continue learning MySQL at any level.




Learn MySQL!




BETWEEN operator range-based filtering is powerful and quite useful in targeting specific rows in a table. Try it out where it makes sense in your queries to learn more.



If you see any mistakes in the code or have any questions, please respond in the comments below. Constructive feedback that helps me provide accurate content is much appreciated.



Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients.
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.



How can I help you?



Free MySQL Query Syntax Mantra PDF cheat sheet. Remember the query syntax order with this mantra.Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Get your brand, product, or service the attention it needs with affordable classified ad placement in the OpenLampTech newsletter.Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.5 Truths I’ve Come To Realize As a Self-taught DeveloperDiscover premium MySQL learning material over in my Kofi Shop today!Disclosure: Some of the services and product links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.



The Newsletter for PHP and MySQL Developers



Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.







Get your brand, product, or service the attention it needs with affordable classified ad placement in the OpenLampTech newsletter. Thank you for your support!
The post Range-based Filtering With the BETWEEN Operator in MySQL appeared first on Digital Owl's Prose.

Most Popular in Database