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;
BookId | BookName | Price | Author | publishedDate |
---|---|---|---|---|
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 |
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;
BookId | BookName | Price | Author | publishedDate |
---|---|---|---|---|
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 |
6 | Head first php mysql | 22 | Lynn Beighley | 2017-07-31 |
7 | Head first sql | 11 | Lynn Beighley | 2017-09-10 |
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;
BookId | BookName | Price | Author | publishedDate |
---|---|---|---|---|
5 | Murach's Java Programming | 62 | Joel Murach | 2017-07-28 |
8 | HTML5 for IOS and Android: A Beginner's Guide | 4 | Robin Nixon | 2017-09-12 |
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');
BookId | BookName | Price | Author | publishedDate |
---|---|---|---|---|
3 | PHP and MySQL Web Development | 12 | Luke Welling | 2017-06-14 |
6 | Head first php mysql | 22 | Lynn Beighley | 2017-07-31 |
7 | Head first sql | 11 | Lynn Beighley | 2017-09-10 |
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';
BookId | BookName | Price | Author | publisherDate |
---|---|---|---|---|
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 |
In this article, we have explained how to use the BETWEEN operator to test if a value falls within a range of values.