MySQL Not Between Operator

The MySQL Not Between Operator returns the rows or records whose values are not between the given two values or range.

For example, If you want to find the 2018 Sales. However, you might not interested in the April and May Sales then you can use this MySQL Not Between with April and May as the Values.

TIP: This operator is exactly opposite to Between Operator.

MySQL Not Between Operator Syntax

The basic syntax of the MySQL Not Between operator can be written as:

SELECT Column_Names 
FROM Table_Name
WHERE Column_Value NOT BETWEEN Value1 AND Value2

This 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.

In this article we will show you, How to use the MySQL Not Between Operator with an examples.

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 and 9 so, they return 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;

OUTPUT

In this example, we will show you, How to use this Not Between operator on String data.

The first statement returns 1, 1 because d is not between a and c , and t is not between u and g. Within the second statement, it will convert the string 10 to int and check 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';

OUTPUT

MySQL Not Between Operator On Numeric and String Data Example

For this MySQL Not Between operator demonstration, We are going to use this Customer table. The below screenshot shows you the data present inside this table

The following 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;

OUTPUT

This MySQL Not Between operator example returns the Customers whose First Name is Not between Gail and Peter. It returns the first names that starts 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 single character instead of writing the complete name. Because, this not between operator considers the first character as a reference.

OUTPUT

MySQL Not Between Dates Example

In this MySQL Not Between Dates example, we will find 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';

OUTPUT

Thank You for Visiting Our Blog