The lower and upper limits specified in between clause of a select statement are inclusive.

The SQL between operators is used to determine or fetch results within a specific range of values. The between operator is available in Standard SQL and is adopted by most database engines.

By default, the between operator is inclusive. This means that it will include the start and end values of the provided range.

Table of Contents Show

SQL Between Operator
SQL Between Dates
Final Thoughts
A Call To Action!

Let us explore how to use the between operator in SQL.

SQL Between Operator

One common use of the between operator is in conjunction with the where clause. This allows you to filter for results that match the specified range.

SELECT column_name(s)
FROM TABLE_NAME
WHERE column_name BETWEEN start_expression AND end_expression

The above query allows you to filter for results that match the range within the specified column.

For example, assume that we have a table as shown below:

Let us use the between operator to get the results where the price is between 50 and 1000. We can run a code as shown below:

SELECT
*
FROM
sample.inventory
WHERE
price BETWEEN 50 AND 1000;

We select all the columns from the inventory table in the sample database in the query. We then use the where clause to filter the prices between 50 and 1000.

This should return a result:

SQL Between Dates

Another common use of the between operator is filtering for matching records on a specific date range.

Consider the example shown below:

SELECT
*
FROM
sample.inventory
WHERE
price BETWEEN '2021-01-01'
AND '2021-12-31';

The above query filters for records that are between January 1st 2021 to December 31st 2021.

Final Thoughts

In this article, the between operator allows you to filter for results that match within a date or numerical range. It also provides testing whether an expression falls within a set range.

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…

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

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

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 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:

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’:

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.

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?

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.

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!