MySQL BETWEEN Operator

Introduction to MySQL BETWEEN operator

This MySQL tutorial explains how to use the MySQL BETWEEN operator with syntax and examples. The MySQL BETWEEN operator selects values within a given range in a SELECT, INSERT, UPDATE, and DELETE statement. The values can be numbers, text, or dates.

BETWEEN operator Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

For the demonstration, we will create a table named books with five columns: BookId, BookName, Price, Author and publishedDate.

CREATE TABLE IF NOT EXISTS `books` ( 
    `BookId` INT(11) NOT NULL AUTO_INCREMENT, 
    `BookName` VARCHAR(255) NOT NULL, 
    `Price` tinyint(4)  NOT NULL, 
    `Author` VARCHAR(255) NOT NULL,
    `publishedDate` date NOT NULL, 
    PRIMARY KEY (`BookId`)
);

INSERT INTO `books` (`BookId`, `BookName`, `Price`, `Author`, `publishedDate`) 
VALUES ('1', 'Learning PHP, MySQL, and JavaScript', '17', 'Robin Nixon', '2017-02-02'), 
       ('2', 'Ubuntu: Up and Running', '23', 'Robin Nixon', '2017-03-23'),
	   ('3', 'PHP and MySQL Web Development', '12', 'Luke Welling', '2017-06-14'),
	   ('4', 'Murach\'s PHP and MySQL', '14', 'Joel Murach', '2017-06-17'),
	   ('5', 'Murach\'s Java Programming', '62', 'Joel Murach', '2017-07-28'),
	   ('6', 'Head first php mysql', '22', 'Lynn Beighley', '2017-07-31'),
	   ('7', 'Head first sql', '11', 'Lynn Beighley', '2017-09-10'),
	   ('8', 'HTML5 for IOS and Android: A Beginner\'s Guide', '4', 'Robin Nixon', '2017-09-12');

To understand MySQL BETWEEN operator, consider an “books” table, which is having the following records:

SELECT * FROM books;

BETWEEN Example

The following MySQL statement selects all books with a price BETWEEN 10 and 25:

SELECT * FROM books
WHERE Price BETWEEN 10 AND 25;

NOT BETWEEN Example

To display the books outside the range of the previous example, use NOT BETWEEN:

SELECT * FROM books
WHERE Price NOT BETWEEN 10 AND 25;

BETWEEN with IN Example

The following MySQL statement selects all books with a price BETWEEN 10 and 25. In addition; do not show books with an Author of Robin Nixon, Joel Murach.

SELECT * FROM books
WHERE (Price BETWEEN 10 AND 25)
AND NOT Author IN ('Robin Nixon','Joel Murach');

BETWEEN Dates Example

The following MySQL statement selects all books published BETWEEN ’01-February-2017′ and ’15-June-2017′:

SELECT * FROM books 
WHERE publishedDate BETWEEN '2017-02-01' AND '2017-06-15';

In this article, we have explained how to use the BETWEEN operator to test if a value falls within a range of values.