BETWEEN

The BETWEEN operator is a logical operator that allows you to specify whether a value in a range or not. The BETWEEN operator is often used in the WHERE clause of the SELECT, UPDATE, and DELETE statements.

The following illustrates the syntax of the BETWEEN operator:

expr [NOT] BETWEEN begin_expr AND end_expr;

The expr is the expression to test in the range defined by begin_expr and end_expr. All three expressions: expr, begin_expr, and end_expr must have the same data type.

The BETWEEN operator returns true if the value of the expr is greater than or equal to (>=) the value of begin_expr and less than or equal to (<= ) the value of the end_expr, otherwise, it returns zero.

The NOT BETWEEN returns true if the value of expr is less than (<) the value of the begin_expr or greater than (>)the value of the value of end_expr, otherwise, it returns 0.

If any expression is NULL, the BETWEEN operator returns NULL .

In case you want to specify an exclusive range, you can use the greater than (>) and less than (<) operators instead.

MySQL BETWEEN operator examples

Let’s practice with some examples of using the BETWEEN operator.

1) Using MySQL BETWEEN with number examples

See the following products table in the sample database:

The following example uses the BETWEEN operator to find products whose buy prices between 90 and 100:

SELECT 
    productCode, 
    productName, 
    buyPrice
FROM
    products
WHERE
    buyPrice BETWEEN 90 AND 100;