MySQL NOT BETWEEN Operator; In this tutorial, we would like to share with you how to use not between logical operator of MySQL in the query.
In this tutorial, we will use the user’s table for fetch filter data of the database table. We will take examples of MySQL NOT BETWEEN with dates and numbers.
In the previous tutorial, we have discussed mysql between logical operator. If you want to learn mysql between click here.
MySQL NOT BETWEEN Operator with Examples
MySQL NOT BETWEEN operator is a logical operator that allows you to specify whether there is a outside the range.
If you want to fetch 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
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.
MySQL NOT BETWEEN operator examples
The following example uses the NOT BETWEEN operator to find product whose id not between 100 and 108 :
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 |
MySQL NOT BETWEEN with dates example
After this, let’s see how you will use MySQL NOT 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 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.
