MySQL - How to select rows where value is in array?

MySQL - How to select rows where value is in array?

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

MySQL - How to select rows where value is in array?

SQL's IN operator works intuitively when you are working with a static list of literals: SELECT * FROM table WHERE id IN (1, 2, 3). However, database queries often need to filter against dynamic data collected from the application layer or stored in another column. If you attempt to pass an array directly into the query like WHERE 3 IN (ids), expecting a single-column comparison to handle an array of values, standard SQL semantics will fail. The database engine interprets (ids) as a subquery returning a single column, not a set of scalar values for comparison in that specific syntax context.

To solve this, you must fundamentally change how the data is structured or how the query logic is constructed. There is no native magic operator in standard MySQL that accepts an array literal directly inside the IN clause without prior preparation. The solution depends entirely on whether your "array" of values resides in memory within your application code or if it needs to be stored in the database itself.

Application-Side Preparation with Prepared Statements

The most robust and secure approach is to handle the array logic outside the database query. In languages like PHP, Python, or JavaScript, you iterate over your array and construct a dynamic IN clause.

If you are using PDO for prepared statements in PHP, you cannot simply pass an array as a single binding for the IN operator. You must loop through the values and bind them individually. However, this approach faces a critical limitation: the number of placeholders (?) is determined at runtime, which prevents standard parameterized query generation in many ORMs or raw PDO contexts without dynamic string building.

Here is a conceptual example using pure PHP to build the query safely:

$ids = [1, 2, 3];
$placeholders = implode(',', array_fill(0, count($ids), '?'));

$sql = "SELECT * FROM users WHERE id IN ($placeholders)";
$stmt = $pdo->prepare($sql);
$stmt->execute($ids);

This method is efficient because it pushes the filtering logic to the database engine, which is optimized for IN operations. It avoids the performance penalty of pulling all rows and filtering in PHP (which would be an $O(N^2)$ operation if naive).

Storing Arrays in a Database Column

If your use case requires passing an array from one query context to another within SQL itself—for instance, comparing against a column that already stores lists—you are entering the realm of JSON or delimited string storage. MySQL 5.7+ introduced native JSON support, which makes this scenario manageable without external loops.

Suppose you have a column json_tags storing arrays as JSON strings like "[1,2,3]". You cannot compare a scalar 3 directly to the array object using standard operators. Instead, you must use the JSON functions to extract individual values or match the entire set.

To check if a specific value exists within a JSON array stored in a column:

SELECT * FROM users 
WHERE JSON_CONTAINS(json_tags, '3');

JSON_CONTAINS returns 1 (true) if the value is found within the JSON document. This function handles the iteration internally on the server side. If you need to check for multiple values simultaneously, JSON_SEARCH allows querying against a pattern, or you can utilize JSON_OVERLAPS in MySQL 8.0.24+ to compare two JSON arrays directly (e.g., checking if an incoming array overlaps with the stored one).

The Delimited String Alternative

In legacy systems or environments without JSON support, arrays are often serialized into comma-separated strings (CSV), such as '1,2,3'. While this mimics an array, it introduces significant pitfalls. A simple LIKE operator fails here because id LIKE '1,2,3' will match id = 123 just as easily.

To safely query CSV columns, you must use FIND_IN_SET:

SELECT * FROM users 
WHERE FIND_IN_SET('3', ids_column) > 0;

This function searches for the position of a substring in a delimited string. It returns 0 if not found and an integer greater than 0 otherwise. While functional, this approach is generally discouraged for production systems because it violates First Normal Form (1NF), leading to harder-to-maintain queries and inefficient indexing strategies.

Performance Considerations

When choosing between application-side binding and database-side JSON functions, performance is the deciding factor. Passing a large array from your application to the database via prepared statements allows the DBMS to use indexes on the primary key (e.g., id). This results in an $O(\log N)$ lookup complexity. Conversely, if you store arrays as JSON or CSV strings, standard B-tree indexes often cannot be used effectively for containment queries unless specific composite indexes are created, potentially degrading query speed as data grows.

For high-volume applications, storing the array of values in a separate pivot table (a many-to-many relationship) remains the gold standard for relational integrity and performance, allowing you to use a simple WHERE id IN (...) with indexed lookups regardless of how the list is initially generated.

Our network