MySQL BETWEEN

 MySQL BETWEEN

Summary: in this tutorial, you will learn how to use the MySQL BETWEEN operator to determine whether a value is in a range of values.

Introduction to MySQL BETWEEN Operator

The BETWEEN the operator is a logical operator that allows you to specify whether a value is 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 the 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 the 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;

This query uses the greater than or equal (>=) and less than or equal ( <= ) operators instead of the BETWEEN operator to get the same result:

SELECT 
    productCode, 
    productName, 
    buyPrice
FROM
    products
WHERE
    buyPrice >= 90 AND buyPrice <= 100;

To find the product whose buy price is not between $20 and $100, you combine the BETWEEN operator with the NOT operator as follows:

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

You can rewrite the query above using the less than (<), greater than (>), and logical operators ( AND) as the following query:

SELECT 
    productCode, 
    productName, 
    buyPrice
FROM
    products
WHERE
    buyPrice < 20 OR buyPrice > 100;

2) Using MySQL BETWEEN with dates example

When you use the BETWEEN an operator with date values, to get the best result, you should use the type cast to explicitly convert the type of column or expression to the DATE type.

The following example returns the orders which have the required dates between 01/01/2003 to 01/31/2003:

SELECT 
   orderNumber,
   requiredDate,
   status
FROM 
   orders
WHERE 
   requireddate BETWEEN 
     CAST('2003-01-01' AS DATE) AND 
     CAST('2003-01-31' AS DATE);

Because the data type of the required date column is DATE so we used the CAST operator to convert the literal strings '2003-01-01' and '2003-12-31' to the DATE values.

In this tutorial, you have learned how to use the MySQL BETWEEN operator to test if a value falls within a range of values.