IN Clause in SQL

The IN clause in SQL is used to specify multiple values in a WHERE condition. It allows you to filter rows by checking if a column's value matches any value in a given list.

---

Key Points

1. Simplifies Multiple OR Conditions:
   The IN clause is a more concise and readable way to match a column against multiple values compared to using multiple OR conditions.

2. Supports Subqueries:
   The IN clause can also be used with subqueries to filter rows based on results from another query.

---

Syntax

Basic Syntax

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

Example

Using a List of Values

SELECT first_name, last_name, department
FROM employees
WHERE department IN ('HR', 'Finance', 'IT');

• Retrieves employees who work in the 'HR', 'Finance', or 'IT' departments.

Using NOT IN

SELECT first_name, last_name
FROM employees
WHERE department NOT IN ('HR', 'Finance');

• Retrieves employees who are not part of the 'HR' or 'Finance' departments.

---

When to Use the IN Clause

• To filter rows against a predefined list of values.

• To dynamically filter rows based on the result of a subquery.

• To improve readability and maintainability when checking for multiple values.

Note: If any value in the list is NULL, special consideration is required as it can affect the query results depending on database behavior.