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.

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.

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.

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'.

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.

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.

---

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.