MySQL : IN Operator

In MySQL, the `IN` operator is used in the `WHERE` clause to check if a value matches any value in a list of specified values. It is often used as a shorthand for multiple `OR` conditions. Here's how it works:

Basic Syntax:


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

  • column1, column2, ...: The columns you want to retrieve data from.
  • table_name: The name of the table you want to retrieve data from.
  • column_name: The column you want to compare values against.
  • (value1, value2, ...): A list of values to compare against.

Example:


SELECT * FROM products WHERE category_id IN (1, 2, 3);
 

This query will retrieve all products where the `category_id` is either 1, 2, or 3.

Using with Subqueries:

You can also use subqueries with the IN operator to retrieve values from a subquery result set.

Example:

SELECT * FROM orders WHERE product_id IN (SELECT product_id FROM products WHERE category_id = 1);
 

This query will retrieve all orders for products belonging to the category with `category_id` equal to 1.

The IN operator provides a concise way to filter rows based on a set of values. It can often lead to more readable and efficient queries compared to using multiple OR conditions.