MySQL NOT IN Operator

The MySQL NOT IN Operator used to check an expression against the Values. The SELECT Statement selects the records that are not matching with the values in MySQL 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 Rows 0

MySQL NOT IN Operator Example

In this example, we are checking whether the numeric values and string data is 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 NOT IN Example 1

MySQL NOT IN Operator 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 2

NOT IN String Example 3

This time, we used the String data inside the NOT IN. This MySQL 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');
MySQL NOT IN Operator 3

This query returns Customers whose Occupation is not Clerical, Professional, and 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