MySQL BETWEEN Operator

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

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