MySQL NOT IN Operator

MySQL NOT IN Operator

The MySQL NOT IN operator is used to filter records that do not match any value in a specified list. This operator is essential for querying data that does not match multiple possible values in SQL queries.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column1 NOT IN (value1, value2, ..., valueN);

The NOT IN operator has the following components:

  • column1, column2, ...: The columns to be retrieved.
  • table_name: The name of the table from which to retrieve the data.
  • column1 NOT IN (value1, value2, ..., valueN): The condition to filter the records, where column1 does not match any value in the specified list.

Example MySQL NOT IN Operator

Let's look at some examples of the MySQL NOT IN operator:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

This query creates a table named employees with columns for id, first_name, last_name, department, and salary.

MySQL CREATE TABLE

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('John', 'Doe', 'HR', 50000.00),
       ('Jane', 'Smith', 'IT', 60000.00),
       ('Jim', 'Brown', 'IT', 55000.00),
       ('Jake', 'White', 'HR', 52000.00),
       ('Jill', 'Green', 'Marketing', 45000.00);

This query inserts five rows into the employees table.

MySQL INSERT INTO TABLE

Step 4: Using NOT IN with WHERE Clause

Use the NOT IN operator to filter records that do not match a list of values:

SELECT * 
FROM employees 
WHERE department NOT IN ('IT', 'Marketing');

This query retrieves all columns from the employees table where the department is neither 'IT' nor 'Marketing'.

MySQL NOT IN WITH WHERE CLAUSE

Step 5: Using NOT IN with Numeric Values

Use the NOT IN operator with numeric values:

SELECT * 
FROM employees 
WHERE salary NOT IN (45000, 50000, 60000);

This query retrieves all columns from the employees table where the salary is neither 45000, 50000, nor 60000.

MySQL NOT IN WITH NUMERIC VALUES

Step 6: Using NOT IN with Multiple Conditions

Use the NOT IN operator with multiple conditions:

SELECT * 
FROM employees 
WHERE department = 'IT' AND salary NOT IN (55000, 60000);

This query retrieves all columns from the employees table where the department is 'IT' and the salary is neither 55000 nor 60000.

MySQL NOT IN WITH MULTIPLE CONDITIONS

Conclusion

The MySQL NOT IN operator is a powerful tool for filtering records that do not match any value in a specified list. Understanding how to use the NOT IN operator is essential for effective data querying and analysis in MySQL.