MySQL NOT BETWEEN operator; Through this tutorial, i am going to show you how to use not between logical operator of MySQL in the query.
MySQL NOT BETWEEN Operator
The MySQL Not Between Operator returns the rows or records whose values are not between given values or range
If you want to fetch or filter data using some condition, you can use MySQL NOT BETWEEN logical operator conditions. And also use MySQL NOT BETWEEN operators with WHERE clauses of SELECT, UPDATE and DELETE statements of MySQL.
Syntax of MySQL NOT BETWEEN Operator
The syntax for the MySQL NOT BETWEEN is:
expression NOT BETWEEN value1 AND value2;
If expression is not greater than or equal to value1 and expression is not less than or equal to value2, BETWEEN returns 1, otherwise, it returns 0.
Example 1 – MySQL NOT BETWEEN Operator
The following example uses the MySQL NOT BETWEEN operator to find product whose id not between 100 and 108; as follows:
SELECT Id, ProductName, UnitPrice FROM Product WHERE UnitPrice NOT BETWEEN 5 AND 100 ORDER BY UnitPrice
Output
| Id | ProductName | UnitPrice |
|---|---|---|
| 33 | Geitost | 2.50 |
| 24 | Guaraná Fantástica | 4.50 |
| 29 | Thüringer Rostbratwurst | 123.79 |
| 38 | Côte de Blaye | 263.50 |
To get the same results, NOT BETWEEN uses less (<) and more (>) operators instead of the operator:
SELECT * FROM users WHERE id < 5 AND id > 100;
| Id | ProductName | UnitPrice |
|---|---|---|
| 33 | Geitost | 2.50 |
| 24 | Guaraná Fantástica | 4.50 |
| 29 | Thüringer Rostbratwurst | 123.79 |
| 38 | Côte de Blaye | 263.50 |
Example 2 – MySQL NOT BETWEEN with dates
The following of example uses Not BETWEEN condition to obtain the value within the date range; as follows:
SELECT name,created_at FROM users WHERE estd NOT BETWEEN '1950-01-01' AND '1975-12-31';
Output
+-------------+----------------+-------------------+--- | id | name | created_at | +------------+-----------+-----+-------------------+--- | 100 | John Smith | 1969-12-25 | | 101 | Robert | 1985-10-01 | | 102 | Gelvin | 1975-09-05 | | 103 | Ronald | 1948-07-10 | | 104 | Kelvin Row | 1975-01-01 | | 105 | Kevin | 1990-12-10 | | 106 | Robin | 1950-07-15 | | 107 | Refel | 2000-01-01 | +-------------+----------------+-------------------+---
This MySQL NOT 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 NOT BETWEEN logical operator tutorial, you have learned how to use NOT BETWEEN logical operator with numbers and dates.