MySQL IN Clause
The IN operator tests whether a value matches any value in a list. It replaces long chains of OR conditions with one short, readable condition, and it can also take its list from a sub-query.
1. Syntax
expression IN (value1, value2, ...)
expression NOT IN (value1, value2, ...)
expression IN (SELECT column FROM ...)x IN (a, b) is true when x equals any value in the list; it means x = a OR x = b.2. IN with a List of Values
SELECT order_id, status, amount FROM orders WHERE status IN ('shipped', 'delivered');Result: 4 rows
| order_id | status | amount |
|---|---|---|
| 101 | shipped | 1200.00 |
| 103 | delivered | 3150.00 |
| 105 | delivered | 780.00 |
| 107 | shipped | 99.00 |
The same with OR, which gets long quickly:
SELECT order_id, status FROM orders WHERE status = 'shipped' OR status = 'delivered';Result: 4 rows
| order_id | status |
|---|---|
| 101 | shipped |
| 103 | delivered |
| 105 | delivered |
| 107 | shipped |
SELECT name, state FROM customers WHERE state IN ('Kerala', 'Goa', 'Sikkim'); -- Sikkim just matches nothingResult: 4 rows
| name | state |
|---|---|
| Ashok | Kerala |
| Bindu | Goa |
| Damini | Kerala |
| Fiona | Goa |
3. IN with Numbers and Dates
SELECT order_id, amount FROM orders WHERE order_id IN (101, 105, 107);Result: 3 rows
| order_id | amount |
|---|---|
| 101 | 1200.00 |
| 105 | 780.00 |
| 107 | 99.00 |
order_id IN ('101', '105') works because MySQL converts the strings, but comparing different types can stop index use and cause surprises.4. NOT IN
SELECT order_id, status FROM orders WHERE status NOT IN ('cancelled', 'pending');Result: 4 rows
| order_id | status |
|---|---|
| 101 | shipped |
| 103 | delivered |
| 105 | delivered |
| 107 | shipped |
Watch out for NULL in the column: Fiona's tier is NULL, and NULL NOT IN (…) is NULL, not TRUE, so she is left out:
SELECT name, tier FROM customers WHERE tier NOT IN ('gold');Result: 3 rows
| name | tier |
|---|---|
| Bindu | silver |
| Damini | bronze |
| Ehsan | silver |
SELECT name, tier FROM customers WHERE tier NOT IN ('gold') OR tier IS NULL;Result: 4 rows
| name | tier |
|---|---|
| Bindu | silver |
| Damini | bronze |
| Ehsan | silver |
| Fiona | NULL |
5. IN with a Sub-query
The list can come from another query. Customers who have at least one delivered order:
SELECT name FROM customers
WHERE cust_id IN (SELECT cust_id FROM orders WHERE status = 'delivered');Result: 2 rows
| name |
|---|
| Ashok |
| Damini |
Customers from states that have a gold customer:
SELECT name, state FROM customers
WHERE state IN (SELECT state FROM customers WHERE tier = 'gold');Result: 3 rows
| name | state |
|---|---|
| Ashok | Kerala |
| Cyrus | Punjab |
| Damini | Kerala |
6. The NOT IN + NULL Trap
Find customers with no orders. Order 107 has a NULL cust_id, and that single NULL makes NOT IN return nothing at all:
SELECT name FROM customers
WHERE cust_id NOT IN (SELECT cust_id FROM orders);Result: Empty set (0 rows)
| name |
|---|
| No rows |
Why? 5 NOT IN (1, 2, NULL) means 5 <> 1 AND 5 <> 2 AND 5 <> NULL, and the last part is NULL, so the whole condition is never TRUE:
SELECT 5 NOT IN (1, 2) AS without_null, 5 NOT IN (1, 2, NULL) AS with_null;Result: 1 row
| without_null | with_null |
|---|---|
| 1 | NULL |
Fix it by excluding NULLs, or use NOT EXISTS, which doesn't have this problem:
SELECT name FROM customers
WHERE cust_id NOT IN (SELECT cust_id FROM orders WHERE cust_id IS NOT NULL);Result: 2 rows
| name |
|---|
| Ehsan |
| Fiona |
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.cust_id);Result: 2 rows
| name |
|---|
| Ehsan |
| Fiona |
NOT EXISTS or a LEFT JOIN … IS NULL.7. Matching Pairs of Values
IN works with row constructors to match several columns together:
SELECT order_id, cust_id, status FROM orders
WHERE (cust_id, status) IN ((1, 'pending'), (2, 'pending'));Result: 2 rows
| order_id | cust_id | status |
|---|---|---|
| 102 | 2 | pending |
| 106 | 1 | pending |
8. IN vs OR vs EXISTS vs JOIN
| Approach | Best for | Notes |
|---|---|---|
IN (list) | A fixed list of values | Readable; the optimizer sorts the list and uses an index |
OR chain | Conditions on different columns | Same result as IN for one column, harder to read |
IN (sub-query) | Membership in another table | MySQL usually rewrites it as a semi-join, which is fast |
EXISTS | "Is there any matching row?" | Handles NULLs safely in the NOT form |
JOIN | When you also need columns from the other table | May duplicate rows if several matches exist |
9. IN Lists from Java
A PreparedStatement needs one ? per value, so build the placeholders to match the list size:
List<String> statuses = List.of("shipped", "delivered");
String placeholders = String.join(", ", Collections.nCopies(statuses.size(), "?"));
String sql = "SELECT order_id, status FROM orders WHERE status IN (" + placeholders + ")";
try (PreparedStatement ps = con.prepareStatement(sql)) {
for (int i = 0; i < statuses.size(); i++) {
ps.setString(i + 1, statuses.get(i)); // values are still bound safely
}
try (ResultSet rs = ps.executeQuery()) { /* ... */ }
}
// Spring Data JPA: List<Order> findByStatusIn(Collection<String> statuses);10. Practice Exercises
Exercise 1: Orders from customers in Kerala (use IN with a sub-query).
SELECT order_id, amount FROM orders
WHERE cust_id IN (SELECT cust_id FROM customers WHERE state = 'Kerala');Result: 4 rows
| order_id | amount |
|---|---|
| 101 | 1200.00 |
| 103 | 3150.00 |
| 105 | 780.00 |
| 106 | 210.00 |
Exercise 2: Customers who have never had a pending order, handling NULLs safely.
SELECT name FROM customers
WHERE cust_id NOT IN (SELECT cust_id FROM orders WHERE status = 'pending' AND cust_id IS NOT NULL);Result: 4 rows
| name |
|---|
| Cyrus |
| Damini |
| Ehsan |
| Fiona |
11. Summary
x IN (a, b, c)is true if x equals any listed value, a short form of OR.- The list can be literal values, row tuples or a sub-query.
NOT INexcludes values, but a NULL in the column or the list makes the result NULL.- Prefer
NOT EXISTSwhen the sub-query can return NULL. - From Java, generate one
?per value and bind them.