How to Parameterize an SQL IN Clause

1. Overview

The IN clause is a powerful tool for filtering data based on a list of values in SQL. However, hardcoding these values directly into the query can make it less flexible and harder to maintain. Parameterizing the IN clause enables passing dynamic sets of values to queries, enhancing both security and maintainability.

In this tutorial, we’ll explore how to parameterize the IN clause across different SQL databases, namely PostgreSQL, SQL Server, and MySQL. Furthermore, we’ll use the Baeldung University database, particularly the Program and Student tables to illustrate practical examples.

2. What Is the IN Operator in SQL

The IN operator filters rows based on a specific list of values. For example, we can retrieve programs offered by specific departments using the IN clause:

SELECT *
FROM Program
WHERE department_id IN (1, 2, 3);
+-----+------------------------------+---------------------------------------+------------+----------+-------+---------------+
| id  | name                         | description                           | start_date | end_date | type  | department_id |
|-----+------------------------------+---------------------------------------+------------+----------+-------+---------------|
| 131 | Theoretical Computer Science | Major in Theoretical Computer Science | 2010-01-01 | <null>   | Major | 1             |
| 132 | Theoretical Computer Science | Minor in Theoretical Computer Science | 2010-01-01 | <null>   | Minor | 1             |
...
| 311 | Dynamics                     | Major in Dynamics                     | 2010-07-01 | <null>   | Major | 3             |
...
| 221 | Signals and Systems          | Major in Signals and Systems          | 2010-07-11 | <null>   | Major | 2             |
...

As is apparent in the output, the query selects all programs from the departments with IDs 1, 2, or 3. This is useful for static lists. Nevertheless, parameterizing the IN clause allows for more dynamic and flexible queries.

3. Parameterizing an IN Clause in PostgreSQL

Let’s look at how we can parameterize the IN clause in the PostgreSQL database.

3.1. Using a Prepared Statement

Prepared statements in PostgreSQL help create parameterized queries that can be executed multiple times with different parameters. This not only improves performance but also enhances security by preventing SQL injection.

For example, let’s use a prepared statement to find programs in specific departments:

PREPARE find_programs_by_departments (text) AS
SELECT *
FROM Program
WHERE department_id IN (SELECT unnest(string_to_array($1, ','))::int);

In this query, we first prepare a statement called find_programs_by_departments. The statement accepts a single text parameter, which will be a comma-separated string of department IDs. Here, the string_to_array function converts this string into an array, and the unnest function expands the array into a set of rows. Notably, the IN clause then compares the department_id against these values. This approach is more flexible than hardcoding values into the query.

To execute the prepared statement with different department IDs, we use the EXECUTE command:

EXECUTE find_programs_by_departments ('1, 2, 3');
+-----+------------------------------+---------------------------------------+------------+----------+-------+---------------+
| id  | name                         | description                           | start_date | end_date | type  | department_id |
|-----+------------------------------+---------------------------------------+------------+----------+-------+---------------|
| 131 | Theoretical Computer Science | Major in Theoretical Computer Science | 2010-01-01 | <null>   | Major | 1             |
| 132 | Theoretical Computer Science | Minor in Theoretical Computer Science | 2010-01-01 | <null>   | Minor | 1             |
| 111 | Operating Systems            | Major in Operating Systems            | 2012-01-01 | <null>   | Major | 1             |
...

The EXECUTE command runs the prepared statement with the provided array of department IDs. The output shows all rows from the Program table where the department_id is either 1, 2, or 3.  Additionally, we can reuse this query with different sets of department IDs without modifying the main SQL query each time, making the code more maintainable and secure.

3.2. Using Functions

We can use a function as another method to parameterize the IN clause. Furthermore, functions encapsulate the logic in a reusable manner, providing an efficient way to handle complex queries and parameterization.

For example, let’s create a function to find programs in specific departments:

CREATE OR REPLACE FUNCTION find_programs_by_departments(dept_ids text)
RETURNS TABLE(id int, name varchar, description varchar, start_date date, end_date date, type varchar, department_id int) AS $$
BEGIN
    RETURN QUERY
    SELECT p.*
    FROM Program p
    WHERE p.department_id IN (SELECT unnest(string_to_array(dept_ids, ','))::int);
END;
$$ LANGUAGE plpgsql;

In this query, we defined a function called find_programs_by_departments that accepts a text input of department IDs (dept_ids). Then, it returns a table with columns matching the Program table. Furthermore, the RETURN QUERY clause executes a SELECT statement that retrieves all rows from the Program table where the department_id matches any value in the dept_ids array obtained from splitting the text input. The use of p as an alias for the Program table resolves any potential ambiguity.

To call this stored procedure with different department IDs, we use the SELECT statement:

SELECT * FROM find_programs_by_departments('1, 2, 3');
+-----+------------------------------+---------------------------------------+------------+----------+-------+---------------+
| id  | name                         | description                           | start_date | end_date | type  | department_id |
|-----+------------------------------+---------------------------------------+------------+----------+-------+---------------|
| 131 | Theoretical Computer Science | Major in Theoretical Computer Science | 2010-01-01 | <null>   | Major | 1             |
| 132 | Theoretical Computer Science | Minor in Theoretical Computer Science | 2010-01-01 | <null>   | Minor | 1             |
| 111 | Operating Systems            | Major in Operating Systems            | 2012-01-01 | <null>   | Major | 1             |
...

The output shows that the stored procedure successfully retrieves programs from the specified departments.

4. Parameterizing an IN Clause in SQL Server

Now, let’s explore how to parameterize the IN clause in SQL Server.

4.1. Using Dynamic SQL

One way to parameterize the IN clause in SQL Server is using dynamic SQL. Dynamic SQL builds and executes SQL statements at runtime.

To parameterize the IN clause using dynamic SQL, we can create a stored procedure that constructs the SQL statement dynamically:

CREATE PROCEDURE find_students_by_ids
    @StudentIds VARCHAR(MAX)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT * FROM Student WHERE id IN (' + @StudentIds + ')'
    EXEC sp_executesql @SQL
END;

In this query, we define a stored procedure find_students_by_ids that accepts a comma-separated string of student IDs (@StudentIds). Then, the @SQL variable constructs the SQL query dynamically, and sp_executesql executes the query.

We use the EXEC command to execute the stored procedure and find students with IDs 1001, 1003, and 1007:

EXEC find_students_by_ids '1001,1003,1007';
+------+--------------+-------------+------------+----------------+----------------+------+
| id   | name         | national_id | birth_date | enrollment_date| graduation_date| gpa  |
+------+--------------+-------------+------------+----------------+----------------+------+
| 1001 | John Liu     | 123345566   | 2001-04-05 | 2020-01-15     | 2024-06-15     | 4.0  |
| 1003 | Rita Ora     | 132345166   | 2001-01-14 | 2020-01-15     | 2024-06-15     | 4.2  |
| 1007 | Philip Lose  | 321345566   | 2001-06-15 | 2020-01-15     | 2024-06-15     | 3.8  |
+------+--------------+-------------+------------+----------------+----------------+------+
3 rows in set (0.004 sec)

This method is highly efficient and provides a clear, structured way to pass lists of values to SQL Server stored procedures.

4.2. Using the STRING_SPLIT Function

Another method for parameterizing the IN clause in SQL Server is using the STRING_SPLIT function. This function splits a string into a table of values, which can be used in the IN clause.

For example, let’s create a stored procedure using the STRING_SPLIT function:

CREATE PROCEDURE find_students_by_ids
    @StudentIds VARCHAR(MAX)
AS
BEGIN
    SELECT *
    FROM Student
    WHERE id IN (SELECT value FROM STRING_SPLIT(@StudentIds, ','))
END;

In this stored procedure, the STRING_SPLIT function splits the @StudentIds string by commas into a table of values, which is then used in the IN clause.

Again, let’s execute the stored procedure with student IDs 1001, 1003, and 1007:

EXEC find_students_by_ids '1001,1003,1007';
+------+--------------+-------------+------------+----------------+----------------+------+
| id   | name         | national_id | birth_date | enrollment_date| graduation_date| gpa  |
+------+--------------+-------------+------------+----------------+----------------+------+
| 1001 | John Liu     | 123345566   | 2001-04-05 | 2020-01-15     | 2024-06-15     | 4.0  |
| 1003 | Rita Ora     | 132345166   | 2001-01-14 | 2020-01-15     | 2024-06-15     | 4.2  |
| 1007 | Philip Lose  | 321345566   | 2001-06-15 | 2020-01-15     | 2024-06-15     | 3.8  |
+------+--------------+-------------+------------+----------------+----------------+------+
3 rows in set (0.002 sec)

It gives the same output as the previous example.

4.3. Creating a User-Defined Table Type

Using a table type is another robust method to parameterize the IN clause in SQL Server. This approach involves creating a user-defined table type and passing it as a parameter to a stored procedure.

First, let’s create a table type to hold the student IDs:

CREATE TYPE StudentIDTableType AS TABLE
(
    StudentID INT
);

Next, we create a stored procedure that accepts this table type as a parameter:

CREATE PROCEDURE find_students_by_ids
    @StudentIDs StudentIDTableType READONLY
AS
BEGIN
    SELECT *
    FROM Student
    WHERE id IN (SELECT StudentID FROM @StudentIDs)
END;

To execute this stored procedure, we declare a variable of the table type, insert the student IDs, and pass it to the stored procedure:

DECLARE @StudentIDs StudentIDTableType
INSERT INTO @StudentIDs (StudentID) VALUES (1001), (1003), (1007);

EXEC find_students_by_ids @StudentIDs
+------+--------------+-------------+------------+----------------+----------------+------+
| id   | name         | national_id | birth_date | enrollment_date| graduation_date| gpa  |
+------+--------------+-------------+------------+----------------+----------------+------+
| 1001 | John Liu     | 123345566   | 2001-04-05 | 2020-01-15     | 2024-06-15     | 4.0  |
| 1003 | Rita Ora     | 132345166   | 2001-01-14 | 2020-01-15     | 2024-06-15     | 4.2  |
| 1007 | Philip Lose  | 321345566   | 2001-06-15 | 2020-01-15     | 2024-06-15     | 3.8  |
+------+--------------+-------------+------------+----------------+----------------+------+
3 rows in set (0.003 sec)

We obtain the same result as before.

5. Parameterizing an IN Clause in MySQL

In MySQL, there are several ways to parameterize the IN clause.

5.1. Using a Prepared Statement

First, let’s see an example of how to use a prepared statement to find students with specific IDs:

SET @StudentIds = '1001,1003,1007';
SET @sql = CONCAT('SELECT * FROM Student WHERE id IN (', @StudentIds, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
+------+-------------+-------------+------------+-----------------+-----------------+------+
| id   | name        | national_id | birth_date | enrollment_date | graduation_date | gpa  |
+------+-------------+-------------+------------+-----------------+-----------------+------+
| 1001 | John Liu    |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
| 1003 | Rita Ora    |   132345166 | 2001-01-14 | 2020-01-15      | 2024-06-15      |  4.2 |
| 1007 | Philip Lose |   321345566 | 2001-06-15 | 2020-01-15      | 2024-06-15      |  3.8 |
+------+-------------+-------------+------------+-----------------+-----------------+------+
3 rows in set (0.003 sec)

In this query, we use CONCAT to construct the SQL query dynamically with the given student IDs, then prepare and execute the statement.

5.2. Using the FIND_IN_SET Function

The FIND_IN_SET function in MySQL can also be used to parameterize the IN clause. This function returns the position of a string within a comma-separated list of strings.

For example, let’s use the FIND_IN_SET function to find students with specific IDs:

SET @student_ids = '1001,1003,1007';

PREPARE find_students_by_ids FROM 
"SELECT * FROM Student WHERE FIND_IN_SET(id, ?)";

Here, we first set a variable @student_ids with a comma-separated list of student IDs. Then, we prepare a statement called find_students_by_ids that takes a single parameter.

Next, we execute the prepared statement with the parameterized list of student IDs:

EXECUTE find_students_by_ids USING @student_ids;
+------+-------------+-------------+------------+-----------------+-----------------+------+
| id   | name        | national_id | birth_date | enrollment_date | graduation_date | gpa  |
+------+-------------+-------------+------------+-----------------+-----------------+------+
| 1001 | John Liu    |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
| 1003 | Rita Ora    |   132345166 | 2001-01-14 | 2020-01-15      | 2024-06-15      |  4.2 |
| 1007 | Philip Lose |   321345566 | 2001-06-15 | 2020-01-15      | 2024-06-15      |  3.8 |
+------+-------------+-------------+------------+-----------------+-----------------+------+
3 rows in set (0.001 sec)

Here, the FIND_IN_SET function checks if the id of each student is in the comma-separated list of student IDs provided as a parameter.

6. Conclusion

In this article, we’ve explored various ways to parameterize IN clauses in SQL. In particular, we looked at how we can achieve this in PostgreSQL, SQL Server, and MySQL databases. By leveraging these techniques, we can create more dynamic and efficient queries, improving both performance and code maintainability.