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 Series
MySQL 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 deserves 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.
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.
Thank you for reading this post. Please share it with someone else who would enjoy it as well.
Josh Otwell has a passion to grow as a PHP Developer, SQL expert, and technical blogger/writer.
Disclaimer: The majority of examples in this post, are performed in a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Like always, just because you can do something doesn’t mean you should. My opinions are my own.
More ways I can help
- Starting a blog? I use WordPress for the Digital Owl’s Prose blog. Let’s both save money on the plans offered. 💸
- Get your brand, product, or service the attention it deserves with affordable classified ad placement in the OpenLampTech newsletter.
- Need hosting for your next web application or WordPress site? I highly recommend Hostinger and use them to host my niche bass fishing site. The service is second to none and they provide free SSL.
- 🔒5 Truths I’ve Come To Realize As a Self-taught Developer
Disclosure: Some of this blog post’s services and product links 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 deserves with affordable classified ad placement in the OpenLampTech newsletter. Thank you for your support!