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 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 four columns: BookId, BookName, Price, and Author.

CREATE TABLE `books` ( 
    `BookId` INT(11) NOT NULL AUTO_INCREMENT , 
    `BookName` VARCHAR(255) NOT NULL , 
    `Price` tinyint(4)  NOT NULL , 
    `Author` VARCHAR(255) NOT NULL , 
    PRIMARY KEY (`BookId`)
);
INSERT INTO `books` (`BookId`, `BookName`, `Price`, `Author`) 
VALUES ('1', 'Learning PHP, MySQL, and JavaScript', '17', 'Robin Nixon'), 
       ('2', 'Ubuntu: Up and Running', '23', 'Robin Nixon'),
	   ('3', 'PHP and MySQL Web Development', '12', 'Luke Welling'),
	   ('4', 'Murach\'s PHP and MySQL', '14', 'Joel Murach'),
	   ('5', 'Murach\'s Java Programming', '62', 'Joel Murach'),
	   ('6', 'Head first php mysql', '22', 'Lynn Beighley'),
	   ('7', 'Head first sql', '11', 'Lynn Beighley'),
	   ('8', 'HTML5 for IOS and Android: A Beginner\'s Guide', '4', 'Robin Nixon');
);

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

SELECT * FROM books;
BookIdBookNamePriceAuthor
1Learning PHP, MySQL, and JavaScript17Robin Nixon
2Ubuntu: Up and Running23Robin Nixon
3PHP and MySQL Web Development12Luke Welling
4Murach's PHP and MySQL14Joel Murach
5Murach's Java Programming62Joel Murach
6Head first php mysql22Lynn Beighley
7Head first sql11Lynn Beighley
8HTML5 for IOS and Android: A Beginner's Guide4Robin Nixon

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;
BookIdBookNamePriceAuthor
1Learning PHP, MySQL, and JavaScript17Robin Nixon
2Ubuntu: Up and Running23Robin Nixon
3PHP and MySQL Web Development12Luke Welling
4Murach's PHP and MySQL14Joel Murach
6Head first php mysql22Lynn Beighley
7Head first sql11Lynn Beighley

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;
BookIdBookNamePriceAuthor
5Murach's Java Programming62Joel Murach
8HTML5 for IOS and Android: A Beginner's Guide4Robin Nixon

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');
BookIdBookNamePriceAuthor
3PHP and MySQL Web Development12Luke Welling
6Head first php mysql22Lynn Beighley
7Head first sql11Lynn Beighley

BETWEEN Dates Example

Below is a selection from the “orders” table, which is having the following records:

SELECT * FROM orders;
OrderIDCustomerIDOrderDate
122017-02-02
222017-03-23
342017-06-14
452017-06-17
562017-07-28
662017-07-31
772017-09-10
862017-09-12

The following MySQL statement selects all orders with an OrderDate BETWEEN ’01-February-2017′ and ’15-June-2017′:

SELECT * FROM orders 
WHERE OrderDate BETWEEN '2017-02-01' AND '2017-06-15';
OrderIDCustomerIDOrderDate
122017-02-02
222017-03-23
342017-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.

Leave A Reply

Your email address will not be published.