MySQL IN Operator

The MySQL IN Operator lets you match a value against a list (literal list or subquery). It’s shorter, cleaner, and often faster than chaining many OR conditions.

IN Syntax

-- Literal list
expr IN (value1, value2, value3, ...)

-- Negation
expr NOT IN (value1, value2, ...)

-- Subquery list
expr IN (SELECT some_column FROM some_table ...)

Example:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

IN Operator Examples

Demo Database (we’ll use the Customers and Suppliers tables)

Below is the SQL statement that selects all customers that are located in “Germany” or “UK”:

SELECT * FROM Customers
WHERE Country IN ('Germany', 'UK');

Result:

CustomerID	CustomerName	ContactName	Address	City	Postal Code	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK

Below is the SQL statement that selects all customers that are NOT located in “Germany” or “UK”:

SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'UK');

Result:

CustomerID	CustomerName	ContactName	Address	City	Postal Code	Country
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Lulea	S-958 22	Sweden
6	Chop-suey Chinese	Yang Wang	Hauptstr. 29	Bern	3012	Switzerland
7	Cactus Comidas para llevar	Patricio Simpson	Cerrito 333	Buenos Aires	1010	Argentina

Below is the SQL statement that selects all customers that are from the same countries as the suppliers:

SELECT * FROM Customers
WHERE Country IN (SELECT country FROM Suppliers);

What this does: Compares rows to a dynamic list generated by the subquery. Performance tip: Put an index on the filtered column (e.g., Customers.Country, Suppliers.Country) for faster IN subquery checks.

Important Notes (Wildcards, IN, BETWEEN)

Wildcards (LIKE)

Leading % (e.g., ‘%abc’) disables index use on B‐Tree indexes. Prefer suffix searches (‘abc%’) for large tables.
Use BINARY or a case‐sensitive collation for case‐sensitive matches.
Only % and _ are supported in MySQL LIKE (no [a-z] ranges; use REGEXP for advanced patterns).
Escape wildcard characters when you need literal matches: ESCAPE '\'.

Cleaner than OR chains: Country IN ('UK','France','Germany').
NOT IN + NULL pitfall: If the list contains NULL, the result is unknown. Prefer NOT EXISTS.
For performance with subqueries, index the compared columns and consider EXISTS or a JOIN.

Conclusion

In this blog, we explored MySQL Wildcards and IN Operator with practical examples. These tools allow you to filter data with precision, whether you are matching patterns, selecting from a set list of values, or choosing values within a specific range.

In our next MySQL blog, we will focus on:

MySQL BETWEEN (advanced usage)
MySQL Aliases for creating shorter and more readable queries.

Request more information

MySQL IN Operator