MySQL IN Operator

📌 MySQL IN Operator

The IN operator in MySQL is used to check whether a value matches any value in a specified list or subquery.

👉 It is a shorter and cleaner alternative to multiple OR conditions.

1️⃣ What is IN Operator?

• Tests membership in a list of values

• Returns TRUE if any value matches

• Can be used with numbers, strings, dates, subqueries

📌 IN improves readability and performance.

2️⃣ Basic Syntax

3️⃣ Simple Example ⭐

✔ Same as:

4️⃣ IN with Numeric Values

5️⃣ IN with Subquery ⭐ (Very Important)

✔ Filters using results of another query

6️⃣ NOT IN Operator

Returns rows not present in the list.

7️⃣ IN with Dates

8️⃣ IN vs OR ⭐ (Interview Favorite)

9️⃣ IN vs EXISTS ❓

• IN is good for small lists

• EXISTS is often better for large subqueries

🔟 Common Mistakes ❌

❌ Forgetting quotes for strings
❌ Using IN with NULL unknowingly
❌ Using NOT IN with NULL values (can return no rows)

⚠️ Example:

➡ Returns no rows due to NULL logic

📌 Interview Questions & MCQs (Very Important)

Q1. What does IN operator do?

A) Joins tables
B) Compares range
C) Matches values in a list
D) Sorts data

✅ Answer: C

Q2. IN operator is equivalent to?

A) AND
B) OR
C) BETWEEN
D) LIKE

✅ Answer: B

Q3. Can IN be used with subqueries?

A) Yes
B) No

✅ Answer: A

Q4. Which operator is opposite of IN?

A) EXISTS
B) NOT EXISTS
C) NOT IN
D) !=

✅ Answer: C

Q5. What happens if NOT IN list contains NULL?

A) Works normally
B) Error
C) Returns no rows
D) Ignores NULL

✅ Answer: C

Q6. Which is better for readability?

A) Multiple OR
B) IN

✅ Answer: B

🔥 Real-Life Use Cases

✔ Filter specific categories
✔ User roles selection
✔ Region-based queries
✔ Subquery-based filtering

✅ Summary

• IN checks value in a list or subquery

• Cleaner alternative to multiple ORs

• Supports numbers, strings, dates

• Beware of NULL with NOT IN

• Important for SQL exams & interviews