IN Operator in MySQL
Introduction
Checking membership in multiple values is cleaner than multiple OR conditions.
The IN operator in MySQL tests if a value matches any in a list or subquery.
It is widely used for:
- Filtering by multiple specific values
- Category or status selection
- Subquery result matching
What is the IN Operator
IN tests if a value exists in a comma-separated list or subquery result set.
Returns TRUE if value matches any list item.
Basic Syntax
With Value List:
sql
SELECT column_name
FROM table_name
WHERE column_name IN (value1, value2, value3);
Example
sql
SELECT * FROM Students
WHERE city IN ('Delhi', 'Mumbai', 'Bangalore');
Returns students from only these 3 cities.
IN with Numbers
Works with numeric values too.
sql
SELECT * FROM Students
WHERE marks IN (95, 98, 100);
Top scorers only.
IN with Subquery
Match against another query's results.
sql
SELECT * FROM Students
WHERE city IN (
SELECT city FROM TopCities
);
IN with Multiple Columns
Row matching (tuple syntax).
sql
SELECT * FROM Students
WHERE (city, department) IN (
('Delhi', 'CSE'), ('Mumbai', 'ECE')
);
IN with NOT
Exclude list membership.
sql
SELECT * FROM Students
WHERE city NOT IN ('Delhi', 'Mumbai');
IN with WHERE Conditions
Combine with other filters.
sql
SELECT * FROM Students
WHERE city IN ('Delhi', 'Mumbai')
AND marks > 80;
IN with ORDER BY
List filter, then sort.
sql
SELECT * FROM Students
WHERE department IN ('CSE', 'IT')
ORDER BY marks DESC;
IN with GROUP BY
Filter before aggregation.
sql
SELECT city, AVG(marks)
FROM Students
WHERE status IN ('Active', 'Provisional')
GROUP BY city;
IN with JOIN
List conditions across tables.
sql
SELECT s.name, c.course_name
FROM Students s
JOIN Courses c ON s.course_id = c.id
WHERE c.duration IN (3, 6, 12);
IN with NULL Handling
NULL in the list makes the entire IN return NULL.
sql
SELECT * FROM Students
WHERE city IN ('Delhi', 'Mumbai', NULL); -- Returns no rows
IN Performance vs OR
IN often uses an index better than multiple OR.
sql
-- Better than: city='Delhi' OR city='Mumbai' OR city='Bangalore'
WHERE city IN ('Delhi', 'Mumbai', 'Bangalore');
Execution Order with IN
Evaluated in the WHERE clause after FROM.
FROM → WHERE (IN lists) → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
Performance Considerations
- IN with small lists uses index
- Large lists: consider a temporary table
- Subquery IN may prevent index usage
- NOT IN with NULL returns no rows
Important Notes
- NULL in IN list returns NULL (no matches)
- Empty IN () always FALSE
- Subquery IN returns a single column only
- Multiple-column IN needs a matching tuple count
- Works with numbers, strings, and dates
Example Scenario
Find top students from premium cities:
sql
SELECT name, marks, city
FROM Students
WHERE city IN ('Delhi', 'Mumbai', 'Bangalore', 'Pune', 'Hyderabad')
AND marks >= 85
ORDER BY marks DESC, city
LIMIT 50;
Common Mistakes
- NULL in IN list (returns no results)
- Large IN lists are hurting performance
- Multi-column IN with wrong tuple count
- Subquery returning multiple columns
- Using IN for ranges (BETWEEN better)
Key Points to Remember
- IN tests membership in a list or subquery
- Cleaner than multiple OR conditions
- NULL in list returns NULL (no matches)
- Single-column subquery only
- Index-friendly for small value lists
- NOT IN excludes list membership
