This SQL tutorial focuses on the MySQL BETWEEN operator, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.
This tutorial is a part of several posts explaining how to use the WHERE clause in MySQL. To read additional posts regarding this subject, please use the following links:
- Introduction – MySQL WHERE clause
- Basic comparison operators
- IN operator
- BETWEEN operator
- LIKE operator
- IS NULL operator
- AND & OR operators
MySQL BETWEEN Operator
The MySQL BETWEEN operator is used to retrieve values based on a certain range.
WHERE column_name BETWEEN lower_value AND upper_value
MySQL BETWEEN Operator with Numeric Values
This MySQL BETWEEN example would retrieve all books whose price is in the range of 50 and 80:
SELECT bookID, bookName, bookPrice FROM books WHERE bookPrice BETWEEN 50 AND 80
MySQL BETWEEN Operator with Character Values
This MySQL BETWEEN example would retrieve all books whose name is in the letter range between A and G (alphabetically, as in a telephone book):
SELECT bookName FROM books WHERE bookName BETWEEN 'A' AND 'G'
- The string values must be enclosed within single quotes (‘string’)
- In MySQL string values are not case-sensitive. The actual value in the column doesn’t have to match the value you specify in the BETWEEN condition. For example, if you specify the letter range D-G and there is a book whose name equals “dune” (first letter is in lowercase), the name of this book will also be retrieved.
MySQL BETWEEN Operator with Date Values
This MySQL BETWEEN example would retrieve all students whose birthdate is between January 1st, 1990 and January 1st, 2000:
SELECT customer_id, first_name, birthdate FROM students WHERE birthdate BETWEEN '1990-01-01' AND '2000-01-01'
- Date values must be enclosed within single quotes (‘date’)
- Date values are format sensitive.
MySQL NOT Operator
This operator is used to produce opposite results than those produced by the BETWEEN operator.
The NOT keyword must be written before the BETWEEN keyword.
This MySQL BETWEEN example would retrieve all books whose price is in the range between 50 and 80:
SELECT bookID, bookName, bookPrice FROM books WHERE bookPrice BETWEEN 50 AND 80
This MySQL BETWEEN example would retrieve all books whose price is not in the range between 50 and 80:
SELECT bookID, bookName, bookPrice FROM books WHERE bookPrice NOT BETWEEN 50 AND 80
General Guidelines
In a MySQL BETWEEN operator, the lower limit must be specified before the upper limit.
SELECT bookName, bookPrice FROM books WHERE bookPrice BETWEEN 80 AND 20 -- No error will be generated and no rows will be returned either.
The MySQL BETWEEN operator is inclusive. For example, when you use the MySQL BETWEEN operator to retrieve the books whose price is in the range between 50 and 90, the result retrieves all of these books, including those whose price equals 50 or 90.
SELECT bookID, bookName, bookPrice FROM books WHERE bookPrice BETWEEN 50 AND 90 -- All books whose price is in the range of 50 and 90 -- Including those whose price equals 50 or 90