IN Operator in MySQL

The IN operator in MySQL is used to filter records based on a list of values. It is a shorthand for using multiple OR conditions in a WHERE clause.

1. Syntax

SELECT column_nameFROM table_nameWHERE column_name IN (value1, value2, value3, ...);

✅ Equivalent to:

SELECT column_nameFROM table_nameWHERE column_name = value1 OR column_name = value2 OR column_name = value3;

2. Example: Using IN with a List of Values

employees Table

Query: Get employees from IT and HR departments

SELECT name, departmentFROM employeesWHERE department IN ('IT', 'HR');

✅ Output:

+---------+-----------+| name    | department |+---------+-----------+| Alice   | IT        || Bob     | IT        || Charlie | HR        || David   | HR        |+---------+-----------+

3. NOT IN Operator

The NOT IN operator filters values that are NOT in the given list.

Query: Get employees NOT from IT or HR

SELECT name, departmentFROM employeesWHERE department NOT IN ('IT', 'HR');

✅ Output:

+------+-----------+| name | department |+------+-----------+| Emma | Sales     |+------+-----------+

4. Using IN with Subqueries

Instead of a fixed list, IN can work with a subquery.

Query: Get employees working in active departments

SELECT nameFROM employeesWHERE department IN (SELECT department FROM departments WHERE status = 'Active');

✅ Explanation:

• Retrieves departments with status = 'Active' from the departments table.
• Fetches employees belonging to those departments.

5. Performance Considerations

🚀 Using IN is faster than multiple OR conditions.
⚠️ Avoid using IN with too many values as it can slow down queries.
🔹 Use indexes on columns used in IN for better performance.

6. IN vs EXISTS

Example: Using EXISTS Instead of IN

SELECT nameFROM employees eWHERE EXISTS (SELECT 1 FROM departments d WHERE d.department = e.department AND d.status = 'Active');

✅ Faster for large datasets because it stops checking after the first match.

7. Key Takeaways

✅ IN simplifies filtering with multiple values.
✅ NOT IN filters out values in a given list.
✅ IN can be used with subqueries.
✅ IN is efficient for small lists but EXISTS is better for large datasets.