MySQL BETWEEN Operator

     MySQL BETWEEN Operator

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included.

BETWEEN Syntax

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

Demo Database

Below is a selection from the “ProductSales” table in the sample database:

BETWEEN Example

The following SQL statement selects all products with a price between 5000 and 10000:

Example

SELECT * FROM ProductSales
WHERE ProductPrice BETWEEN 5000 AND 10000;

NOT BETWEEN Example

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

Example

SELECT * FROM ProductSales
WHERE ProductPrice NOT BETWEEN 5000 AND 10000;

BETWEEN with IN Example

The following SQL statement selects all products with a price between 5000 and 10000. In addition; do not show products A,B, or C:

Example

SELECT * FROM ProductSales
WHERE ProductPrice BETWEEN 5000 AND 10000
AND Product NOT IN (A,B,C);

BETWEEN Text Values Example

The following SQL statement selects all products with a Product between “A” and “C”:

Example

SELECT * FROM ProductSales
WHERE Product BETWEEN 'Bill' AND 'Joe'
ORDER BY Product;

NOT BETWEEN Text Values Example

The following SQL statement selects all products with a Product not between “A” and “C”:

Example

SELECT * FROM ProductSales
WHERE Product NOT BETWEEN 'A' AND 'C'
ORDER BY Product;