📌 Definition

The IN operator is used in a WHERE clause to filter records that match any value in a given list.

👉 It is used when you want to compare a column with multiple possible values.

🧠 Syntax

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

🔑 Core Idea

Instead of writing multiple OR conditions:

column_name = value1 OR column_name = value2 OR column_name = value3

👉 You can write:

column_name IN (value1, value2, value3)

📊 Example Table: Students

1. IN with Numbers

✅ Example

SELECT * FROM Students
WHERE Age IN (18, 20, 23);

📌 Result

• Amit (18)
• Karan (20)
• Neha (23)

💡 Explanation

Returns all rows where Age is either 18, 20, or 23.

2. IN with Strings

✅ Example

SELECT * FROM Students
WHERE Name IN ('Amit', 'Neha');

💡 Explanation

Returns rows where Name matches any value in the list.

3. IN with Subquery

📌 Definition

IN can also be used with a subquery (a query inside another query).

✅ Example

SELECT * FROM Students
WHERE ID IN (SELECT ID FROM GraduatedStudents);

💡 Explanation

• First, the inner query runs.
• Then, matching values are selected from the main table.

4. NOT IN Operator

📌 Definition

Used to exclude values from a list.

🧠 Syntax

SELECT * FROM table_name
WHERE column_name NOT IN (value1, value2);

✅ Example

SELECT * FROM Students
WHERE Age NOT IN (18, 21);

💡 Explanation

Returns students whose age is not 18 or 21.

⚠️ Important Notes

1. Data Types Must Match

Age IN ('18', '20')  -- ❌ Wrong (strings instead of numbers)

Age IN (18, 20)      -- ✅ Correct

2. Works Like Multiple OR Conditions

WHERE Name IN ('Amit', 'Riya')

👉 Same as:

WHERE Name = 'Amit' OR Name = 'Riya'

3. NULL Behavior (Advanced Note)

• IN does not match NULL values.
• Special handling is needed using IS NULL.

📊 Summary Table

🎯 Key Insight

The IN operator:

• Improves readability
• Reduces query length
• Is ideal when checking multiple fixed values