How to Use the IN and NOT IN Operators in MySQL

08/04/2021

Contents

In this article, you will learn how to use the IN and NOT IN operators in MySQL.

Using the IN and NOT IN operators in MySQL

The IN and NOT IN operators in MySQL are used to match a value against a set of values or a subquery. These operators allow you to specify multiple values or a list of values in a single query, which makes it easier to retrieve data from a database.

IN operator

The IN operator is used to match a value against a set of values or a subquery. Here’s the syntax:

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

For example, let’s say we have a table called “employees” with columns “id”, “name”, and “department”. If we want to find all the employees who work in the “Sales” or “Marketing” departments, we can use the following query:

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

This query will return all the names of employees who work in the Sales or Marketing departments.

We can also use the IN operator with a subquery. For example, let’s say we have a table called “orders” with columns “order_id”, “customer_id”, and “order_total”. If we want to find all the orders made by our top 10 customers (based on the total order amount), we can use the following query:

SELECT order_id, order_total
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  GROUP BY customer_id
  ORDER BY SUM(order_total) DESC
  LIMIT 10
);

This query will return the order ID and order total for all the orders made by the top 10 customers.

NOT IN operator

The NOT IN operator is used to match a value against a set of values or a subquery and returns all rows where the value is not in the set. Here’s the syntax:

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

For example, let’s say we have a table called “customers” with columns “id”, “name”, and “country”. If we want to find all the customers who are not from the United States or Canada, we can use the following query:

SELECT name
FROM customers
WHERE country NOT IN ('United States', 'Canada');

This query will return all the names of customers who are not from the United States or Canada.

We can also use the NOT IN operator with a subquery. For example, let’s say we have a table called “orders” with columns “order_id”, “customer_id”, and “order_total”. If we want to find all the orders made by customers who have never made an order with a total amount greater than $1000, we can use the following query:

SELECT order_id, order_total
FROM orders
WHERE customer_id NOT IN (
  SELECT customer_id
  FROM orders
  WHERE order_total > 1000
);

This query will return the order ID and order total for all the orders made by customers who have never made an order with a total amount greater than $1000.

#MySQL