Explain IN Operator in SQL

In SQL, the IN and NOT IN operators are used to filter query results based on a list of values. Here's a breakdown of how each operator works:

IN Operator

The IN operator allows you to specify multiple values in a WHERE clause. It is a shorthand for multiple OR conditions.

Syntax

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

Example

Assume you have a table Employees with the following data:

If you want to select employees who are either in the HR or Sales departments, you would use:

SELECT EmployeeID, Name, Department
FROM Employees
WHERE Department IN ('HR', 'Sales');

Output

NOT IN Operator

The NOT IN operator is the opposite of IN. It filters the results to exclude rows where the column value is in the specified list.

Syntax

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

Example

Using the same Employees table, if you want to select employees who are not in the HR or Sales departments, you would use:

SELECT EmployeeID, Name, Department
FROM Employees
WHERE Department NOT IN ('HR', 'Sales');

Output

In this example, only David is in the IT department, which is not in the list of HR and Sales departments.

Both IN and NOT IN provide a convenient way to filter results based on multiple criteria.