MySQL: NOT BETWEEN Logical Operator Examples

In MySQL NOT BETWEEN logical operator tutorial, we would love to share with you how to use not 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 NOT BETWEEN with dates and numbers.

In previous tutorial, we have discussed about mysql between logical operator. If you want to learn mysql between click here.

MySQL NOT BETWEEN

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

IdProductNameUnitPrice
33Geitost2.50
24Guaraná Fantástica4.50
29Thüringer Rostbratwurst123.79
38Côte de Blaye263.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;
IdProductNameUnitPrice
33Geitost2.50
24Guaraná Fantástica4.50
29Thüringer Rostbratwurst123.79
38Côte de Blaye263.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.

Spread the love

Be First to Comment

Leave a Reply

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