📋 The IN operator in SQL is used to check whether a value matches any value in a specified list. It provides a simpler and more readable alternative to writing multiple OR conditions. The IN operator is commonly used with the WHERE clause to filter records based on multiple possible values.

📖 What is the IN Operator?

The IN operator compares a column value against a list of values. If the column matches any value in the list, the row is included in the result set. This makes queries shorter, easier to read, and easier to maintain.

📝 Basic Syntax

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

📊 Sample Table

Consider the following Students table:

🎯 Using IN with Multiple Values

Retrieve students who belong to either the Computer Science or Physics department.

SELECT *
FROM Students
WHERE Department IN (
    'Computer Science',
    'Physics'
);

This query returns all students whose department matches either value in the list.

⚖️ IN vs Multiple OR Conditions

The IN operator is equivalent to multiple OR conditions but is shorter and easier to read.

SELECT *
FROM Students
WHERE Department = 'Computer Science'
OR Department = 'Physics'
OR Department = 'Mathematics';

SELECT *
FROM Students
WHERE Department IN (
    'Computer Science',
    'Physics',
    'Mathematics'
);

🔗 IN with Numeric Values

The IN operator also works with numeric data.

SELECT *
FROM Students
WHERE StudentID IN (101, 103, 105);

🚫 Using NOT IN

The NOT IN operator retrieves rows whose values are not present in the specified list.

SELECT *
FROM Students
WHERE Department NOT IN (
    'Computer Science',
    'Physics'
);

This query returns students whose department is neither Computer Science nor Physics.

📈 IN with ORDER BY

After filtering rows using IN, the results can be sorted using the ORDER BY clause.

SELECT Name,
       Department,
       Marks
FROM Students
WHERE Department IN (
    'Computer Science',
    'Mathematics'
)
ORDER BY Marks DESC;

🔍 IN with Subqueries

The IN operator can compare values against the results of another query instead of a fixed list.

SELECT Name,
       Department
FROM Students
WHERE Department IN (
    SELECT DepartmentName
    FROM Departments
    WHERE IsActive = TRUE
);

In this example, the inner query returns a list of active departments, and the outer query retrieves students who belong to those departments.

📊 Common Uses of IN

⚠️ IN and NULL Values

Be careful when using NOT IN with lists or subqueries that may contain NULL values. Depending on the data, this can produce unexpected results because comparisons involving NULL evaluate to unknown.

💼 Real-World Example

A university administrator wants to generate a report containing students from the Computer Science, Mathematics, and Physics departments.

SELECT StudentID,
       Name,
       Department,
       Marks
FROM Students
WHERE Department IN (
    'Computer Science',
    'Mathematics',
    'Physics'
)
ORDER BY Department,
         Marks DESC;

This query retrieves students from the selected departments and organizes the results by department and marks.

⚠️ Best Practices

🚀 Key Points to Remember