MySQL NOT BETWEEN Operator

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. 

MySQL 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 WHERE clause.

Syntax of the MySQL NOT BETWEEN Operator

SELECT expressions FROM table_name WHERE column_name NOT BETWEEN value1 AND value2; Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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';Code language: SQL (Structured Query Language) (sql)

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 BETWEEN operator.

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';Code language: SQL (Structured Query Language) (sql)

The MONTH(Date_Joined) extracts only the Month values from the Date values in the Date_Joined column.

We get the output as follows,

Conclusions

BETWEEN and 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.

References

• MySQL official documentation on the BETWEEN operator.
• JournalDev article on the BETWEEN operator.