In this tutorial, we will learn about the MySQL NOT BETWEEN operator. In the MySQL
BETWEEN operator article, we saw how we can return records within a range. It also helped us avoid conditions with the
AND operator and making our code more readable and effective.
NOT BETWEEN is a combination of two operators – The
NOT operator and the
BETWEEN operator. The MySQL
NOT BETWEEN operator does the exact opposite of the
BETWEEN operator. It returns all the values which are not in the specified range. The
NOT BETWEEN operator is used together with the
Syntax of the MySQL NOT BETWEEN Operator
SELECT expressions FROM table_name WHERE column_name NOT BETWEEN value1 AND value2;
Using the MySQL NOT BETWEEN Operator
Consider the following Employee table.
Just like the
BETWEEN operator, we can carry the
NOT BETWEEN operations on numbers, text and dates.
1. Difference between BETWEEN and NOT BETWEEN Operators
Let us first try to understand the difference between the two operators. How about finding out the employees who have their salary between 60000 and 80000?
We use the query,
SELECT * FROM Employee WHERE Salary BETWEEN 60000 AND 80000;
We get the following output,
Let us try to find out the employees who do not have their salary in the range 60000 and 80000. We use the following query,
SELECT * FROM Employee WHERE Salary NOT BETWEEN 60000 AND 80000;
We get the following output,
It is important to note here that
BETWEEN is inclusive of the range. That is, the starting value and the ending value of the range is included in the result-set. In
NOT BETWEEN, that is not the case. As you can see Raj and Radhika have their salary 60000 and 80000 but are not in the result-set for
NOT BETWEEN Operator.
2. Using BETWEEN and NOT BETWEEN Operators together
So you must have had one question by now and that is – can we use the BETWEEN and NOT BETWEEN together? Of course, we can! Let us try to find out employees who have their salary in the range of 60000 and 80000 but their joining date should not be between 1st January 2019 and 1st October 2020. We use the following query,
SELECT * FROM Employee WHERE Salary BETWEEN 60000 AND 80000 AND Date_Joined NOT BETWEEN '2019-01-01' AND '2020-10-01';
We get the output as,
3. NOT BETWEEN Operator with Dates
Using Dates in any language is a whole topic in itself. But let us focus on using them with the
NOT BETWEEN operator for now. The procedure for using ranges with dates is the same as the example we saw in the
BETWEEN operator article.
However, we will take a look at a different example. Note that this example also works with the
So, what if we want to find employees who, irrespective of the year, do not have their joining month between February and October? We are looking at employees who joined the company either in January or after October in any given year.
We use the following query for this,
SELECT * FROM Employee WHERE MONTH(Date_Joined) NOT BETWEEN '02' and '10';
The MONTH(Date_Joined) extracts only the Month values from the Date values in the Date_Joined column.
We get the output as follows,
NOT BETWEEN operators in MySQL are very useful in terms of filtering data by value. I would highly recommend you to check the below links for further reading.