MySQL BETWEEN condition query; In this tutorial, we would love to share with you how to use between logical operator of MySQL in query.
As well as, you will learn how to select data between two dates or numbers in MySQL database using MySQL BETWEEN logical operator with query examples.
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.
