MySQL BETWEEN Condition

MySQL BETWEEN condition; In this tutorial, i am going to show you how to use between logical operator of MySQL in query with the help of examples.

And i will take some example for how to use date between now and days using MySQL between operator.

MySQL BETWEEN Condition

MySQL BETWEEN operator is a logical operator that allows you to specify whether there is a value in a range or not.

Syntax of MySQL BETWEEN Condition

The syntax for the MySQL BETWEEN is:

expression BETWEEN value1 AND value2;

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

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

Example 1 – MySQL BETWEEN Operator

The following example uses the BETWEEN operator to find users whose id between 100 and 108 :

 SELECT name
 FROM users
 WHERE id BETWEEN 100 AND 200;

Output

 +------------+-----------+--------
 | id        | name             |  
 +------------+-----------+--------
 | 100         | John Smith     |
 | 101         | Robert         | 
 | 102         | Gelvin         | 
 | 103         | Ronald         |
 | 104         | Kelvin Row     |
 | 105         | Kevin          |
 | 106         | Robin          |
 | 107         | Refel          |
 | 108         | Merry          |
 +------------+-----------+--------

To get the same results, BETWEEN uses more or equal (>=) and less or less (<=) operators instead of the operator:

 SELECT *
 FROM users
 WHERE id >= 100
 AND id <= 108;

 +------------+-----------+--------
 | id          | name           |  
 +------------+-----------+--------
 | 100         | John Smith     |
 | 101         | Robert         | 
 | 102         | Gelvin         | 
 | 103         | Ronald         |
 | 104         | Kelvin Row     |
 | 105         | Kevin          |
 | 106         | Robin          |
 | 107         | Refel          |
 | 108         | Merry          |
 +------------+-----------+--------

Example 2 – MySQL BETWEEN with dates

After this, let’s see how you will use MySQL BETWEEN operator with dates. When using the BETWEEN position in MySQL with dates, make sure to use the CAST function to change the values in the dates clearly.

The example of the following date uses the condition BETWEEN to obtain the value within the date range.

SELECT 
   name,created_at
 FROM 
    users
 WHERE 
    created_at BETWEEN 
      CAST('2019-01-01' AS DATE) AND 
      CAST('2019-06-31' AS DATE);

Output

 +-------------+----------------+-------------------+---
 | id          | name           |  created_at       |  
 +------------+-----------+-----+-------------------+---
 | 100         | John Smith     |  2019-01-01       |
 | 101         | Robert         |  2019-02-10       |
 | 102         | Gelvin         |  2019-02-15       | 
 | 103         | Ronald         |  2019-03-25       |
 | 104         | Kelvin Row     |  2019-04-28       |
 | 105         | Kevin          |  2019-05-09       |
 | 106         | Robin          |  2019-05-22       |
 | 107         | Refel          |  2019-06-11       |
 | 108         | Merry          |  2019-06-01       |
 +-------------+----------------+-------------------+---

This MySQL BETWEEN condition example would return all records from the created_at table where the created_at is between Jan 1, 2019 and Jun 01, 2019 (inclusive). It would be equivalent to the following SELECT statement:

 SELECT name,created_at
 FROM users
 WHERE created_at >= CAST('2019-01-01' AS DATE)
 AND order_date <= CAST('2019-01-31' AS DATE);

Conclusion

In this MySQL BETWEEN logical operator tutorial, you have learned how to use BETWEEN logical operator with numbers and dates.

Recommended MySQL Tutorials