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 NOT IN operator is quite the opposite of IN Operator. You can call this as NOT (Expression IN (Values).

MySQL NOT IN Operator Syntax

The syntax of the NOT IN operator in MySQL is

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

For the MySQL NOT IN demonstration, We are using the below-shown data

MySQL NOT IN Operator 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'); 
MySQL NOT IN Operator 1

MySQL NOT IN Operator Example 2

The following Not in operator 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 `MySQL Tutorial`.customer
WHERE Income NOT IN (50000, 60000);
MySQL NOT IN Operator 2

MySQL NOT IN String Example 3

This time, we used the String data inside the NOT IN operator. 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 `MySQL Tutorial`.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.

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

Please refer to IN Operator article.