IN and NOT IN operators in MySQL

Introduction

IN and NOT IN help you find rows where a value matches or does not match any value in a list. They make checking multiple values easy and quick.

You want to find customers from specific cities like 'New York', 'Chicago', or 'Los Angeles'.

You want to exclude products that are in certain categories from your results.

You want to check if a student's grade is one of several passing grades.

You want to filter orders that belong to a set of order IDs.

You want to find employees who are not in a list of managers.

Syntax

MySQL

SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);

SELECT column_name(s) FROM table_name WHERE column_name NOT IN (value1, value2, ...);

The list inside IN or NOT IN is a set of values separated by commas and enclosed in parentheses.

IN checks if the value matches any in the list; NOT IN checks if it matches none.

Examples

Finds all employees who work in either Sales or Marketing departments.

MySQL

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

Finds products that are not in Electronics or Toys categories.

MySQL

SELECT * FROM products WHERE category NOT IN ('Electronics', 'Toys');

Finds students whose grade is A, B, or C.

MySQL

SELECT name FROM students WHERE grade IN ('A', 'B', 'C');

Sample Program

This creates a simple employees table, adds four employees, then selects names of employees in Sales or Marketing departments, and then those not in these departments.

MySQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  department VARCHAR(50)
);

INSERT INTO employees VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', 'HR'),
(3, 'Charlie', 'Marketing'),
(4, 'Diana', 'IT');

SELECT name FROM employees WHERE department IN ('Sales', 'Marketing');

SELECT name FROM employees WHERE department NOT IN ('Sales', 'Marketing');

Important Notes

If the list in IN or NOT IN is empty, the query may return no rows or all rows depending on the operator.

NULL values in the list or column can affect results; be careful when NULLs are involved.

IN and NOT IN are easier to read and write than multiple OR or AND conditions.

Summary

IN checks if a value matches any value in a list.

NOT IN checks if a value does not match any value in a list.

They simplify queries that need to compare against multiple values.