MySQL Not Between Operator

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

SELECT Column_Names 
FROM Table_Name
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 between 1 and 7, and 2 is between 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 Not Between 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 Not Between 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 `MySQL Tutorial`.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 `MySQL Tutorial`.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 `MySQL Tutorial`.customer
WHERE HireDate  NOT BETWEEN '2009-01-01 ' AND '2013-01-15';