MySQL NOT IN Operator

The MySQL NOT IN Operator is used to check an expression against the Values. The SELECT Statement selects the records that do not match the values in the NOT IN expression.

The MySQL NOT IN operator is quite the opposite of IN. You can call this as NOT (Expression IN (Values), and the syntax of this one is

SELECT Columns
FROM Tables
WHERE Column_Name NOT IN (Value1,...., ValueN)

For the demonstration, We are using the below-shown data

Customer Table

MySQL NOT IN Operator Example

In this example, we are checking whether the numeric values and string data are present or not. If not, it returns 1 otherwise, 0.

SELECT 4 NOT IN (2, 4, 6, 8, 10);

SELECT 4 NOT IN (2, 6, 8, 10);

SELECT 'abc' NOT IN ('xyz', 'ab', 'hi');
Simple Example 1

NOT IN Example 2

The following Not in query SELECT the Customers whose Income is Not in either 50000 or 60000

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 WHERE Income NOT IN (50000, 60000);
MySQL Not in operator Example 2

MySQL NOT IN String Operator Example 3

This time, we used the String data inside the NOT IN. This query finds the Customers whose Occupation is not Clerical and Professional.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 WHERE Occupation NOT IN ('Clerical', 'Professional');
NOT IN String Example 3

This query returns Customers whose occupations are not Clerical, Professional, or skilled manual. You can also use Order By clause along with this Not In operator. Please refer to IN Example article.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 WHERE Occupation NOT IN ('Clerical', 'Professional', 'Skilled Manual');
MySQL NOT IN Operator 4