The MySQL Not Between Operator returns the records whose values are not between the given two values or range. The MySQL Not between operator is exactly opposite to the Between Operator
For example, If you want to find the 2018 Sales. However, you might not be interested in April and May Sales, then use this MySQL Not Between with April and May as the Values.
MySQL Not Between Operator Syntax
The basic syntax of the MySQL Not Between operator is
WHERE Column_Value NOT BETWEEN Value1 AND Value2
The Not between operator displays the records that are not between the Value1 and Value2 Including them. It mean, Column_Value <= Value1 and Column_Value >= Value2.
MySQL Not Between example
This simple example shows the working functionality of MySQL Not Between on Numerical values.
In the first statement, 3.2 is not Between 1 and 3, and 22 is not between 1 and 9 so, it returns 1. In the next statement, 5 is in the middle of 1 and 7, and 2 is in middle of 1 & 9, so it returns 0.
SELECT 3.2 NOT BETWEEN 1 AND 3, 22 NOT BETWEEN 1 AND 9;
SELECT 5 NOT BETWEEN 1 AND 7, 2 NOT BETWEEN 1 AND 9;
SELECT 2 NOT BETWEEN 3 AND 1, 7 NOT BETWEEN 9 AND 1;
In this MySQL Operator example, we explain to you how to use this Not Between operator on String data.
The first MySQL statement returns 1, because d is not between a and c, and t is not between u and g. Within the second statement, it converts the string 10 to int and tests whether 2 is not between 4 and 10.
SELECT 'd' NOT BETWEEN 'a' AND 'c', 't' NOT BETWEEN 'u' AND 'g';
SELECT 2 NOT BETWEEN 4 AND '10', 7 NOT BETWEEN 5 AND '10';
SELECT 2 NOT BETWEEN 5 AND 'Hello';
MySQL Not Between Operator On Numeric and String Data Example
For this MySQL Not Between operator demonstration, we use this Customer table. The following screenshot shows you the data present inside this table
The following MySQL Operator query returns the Customers whose Income is not between 70000 and 90000. Or, whose Income is less than 70000 and greater than 90000.
SELECT EmpID, `First Name`, `Last Name`, Qualification, Occupation, Income, Sales, HireDate FROM customer WHERE Income NOT BETWEEN 70000 AND 90000;
This MySQL Not Between operator example returns the Customers whose First Name is Not between Gail and Peter. It returns the first names that start with before Alphabet G and after P
SELECT EmpID, `First Name`, `Last Name`, Qualification, Occupation, Income, Sales, HireDate FROM customer WHERE `First Name` NOT BETWEEN 'Gail' AND 'Peter';
TIP: We can also use a single character because this not between operator considers the first character as a reference.
MySQL Not Between Dates Example
In this MySQL Not Between Dates example, we return all the Customers whose Hire Date is not between 2009-01-01 and 2013-01-15
SELECT EmpID, `First Name`, `Last Name`, Qualification, Occupation, Income, Sales, HireDate FROM customer WHERE HireDate NOT BETWEEN '2009-01-01 ' AND '2013-01-15';