MySQL IN Operator

MySQL IN Operator: Matching Against a List or Subquery

The MySQL IN operator checks whether a column value matches any item in a comma-separated list or a single-column subquery. It replaces repetitive OR chains with clean, readable SQL that the optimizer handles using fast range scans.

Basic Syntax

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

You can also pass a SELECT subquery inside the parentheses, provided the subquery returns exactly one column.

Practical SQL Examples

The first query fetches orders in demo_orders with specific fulfillment statuses, and the second filters customers who have placed paid orders:

-- Match a constant list of statuses
SELECT id, customer_id, status, total_amount
FROM demo_orders
WHERE status IN ('paid', 'shipped', 'delivered')
ORDER BY id DESC;

-- Match against a single-column subquery
SELECT id, first_name, email
FROM demo_customers
WHERE id IN (
    SELECT DISTINCT customer_id
    FROM demo_orders
    WHERE total_amount >= 200.00
);

Common Mistakes and Best Practices

• Avoid Empty IN () Lists in App Code: WHERE id IN () causes an immediate SQL syntax error. Check if your parameter array is empty in PHP, Python, or Node.js before running the query.
• Never Pass NULL Into NOT IN: If a subquery inside NOT IN (...) returns any NULL, the entire query returns zero rows. Always filter WHERE col IS NOT NULL or switch to NOT EXISTS.
• Keep IN Lists Reasonable: Putting 50,000 IDs inside IN (...) bloats SQL parsing and packet memory; load large ID sets into a temporary table and JOIN instead.

Frequently Asked Questions

Is IN faster than multiple OR conditions in MySQL?

Yes, MySQL binary-searches sorted constant lists inside IN (...) and executes range scans cleanly, whereas long OR chains generate larger parse trees.

Does IN (3, 1, 2) return rows in that exact order?

No, IN only filters rows; add ORDER BY FIELD(id, 3, 1, 2) if you want results sorted in your list order.

What happens if WHERE status IN ('paid', NULL) runs?

Rows with status = 'paid' still match, while rows with status IS NULL do not match because NULL = NULL is unknown.