MySQL IN Operator

MySQL Tutorial

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.

 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.

 Basic Syntax

 Simple Example

Same as:

IN with Numeric Values

IN with Subquery (Very Important)

  •  Filters using results of another query

 NOT IN Operator

Returns rows not present in the list.

IN with Dates

The IN operator is used to match a column against multiple date values. It is useful when you need to filter records for specific dates. Dates must be written in proper date format (YYYY-MM-DD).

Example

IN vs OR (Interview Favorite)

IN and OR are used to check multiple conditions on the same column. IN is more readable and cleaner when many values are involved. OR becomes lengthy and harder to manage as conditions increase. Performance is usually similar for small value lists.

IN Example

OR Example

IN vs EXISTS (MySQL)

IN compares a value against a list or subquery result. EXISTS checks whether a subquery returns any rows. IN works well with small result sets.EXISTS is more efficient for large datasets and correlated subqueries.

IN Example

EXISTS Example


 

 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

You may also like...