MySQL- BETWEEN Logical Operator Syntax & Examples

In MySQL BETWEEN logical operator tutorial, we would love to share with you how to use between logical operator of MySQL in query.

In this tutorial, we will use users table for fetch filter data of database table. We will take examples of MySQL BETWEEN with dates and numbers.

MySQL BETWEEN

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

If you want to fetch filter data using some condition, you can use MySQL BETWEEN logical operator conditions. And also use MySQL BETWEEN operators with WHERE clauses of SELECT, UPDATE and DELETE statements of MySQL.

Syntax

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.

MySQL BETWEEN operator examples

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          |
 +------------+-----------+--------

MySQL BETWEEN with dates example

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.

Spread the love

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *