2 Ways to Select Rows that Match all Items in a List (T-SQL)

This article presents two ways to select rows based on a list of IDs (or other values) in SQL Server. This can be useful in scenarios where you have a comma-separated list of IDs, and you want to query your database for rows that match those IDs.

Say you have the following list of IDs:

1,4,6,8

And so you now want to query a table for records that have any of those values (i.e. either 1, 4, 6 or 8) in its ID column.

Here are two ways to go about that.

Option 1: The IN Operator

One option is to use the IN operator within your query to return only those rows that are specified in the list.

SELECT ArtistId, ArtistName
FROM Artists
WHERE ArtistId IN (1,4,6,8);

Results:

ArtistId  ArtistName               
--------  -------------------------
1         Iron Maiden              
4         Buddy Rich               
6         Jim Reeves               
8         Maroon 5

As you can see, the values in the ArtistId column match those our the list.

Option 2: The STRING_SPLIT() Function

Starting with SQL Server 2016, the STRING_SPLIT() function can be used to split a character expression using a specified separator. In other words, you can use it to split a comma-delimited list.

Here’s an example using the same data as in the previous example:

SELECT ArtistId, ArtistName
FROM Artists
JOIN STRING_SPLIT('1,4,6,8', ',')
ON value = ArtistId;

Result:

ArtistId  ArtistName               
--------  -------------------------
1         Iron Maiden              
4         Buddy Rich               
6         Jim Reeves               
8         Maroon 5

One benefit of STRING_SPLIT() is that it allows/(requires) you to specify what is being used as the delimiter for the list. This can be handy when your list uses a different character as its delimiter.

Here’s the same example, but for a space-separated list:

SELECT ArtistId, ArtistName 
FROM Artists JOIN STRING_SPLIT('1 4 6 8', ' ') 
ON value = ArtistId;

Result:

ArtistId  ArtistName               
--------  -------------------------
1         Iron Maiden              
4         Buddy Rich               
6         Jim Reeves               
8         Maroon 5

Compatibility Level

Note that STRING_SPLIT() is only available on databases with a compatibility level of 130 or above. You can change a database’s compatibility level with the following statement:

ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = 130;

Or to bring it up to SQL Server 2017 level:

ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = 140;

Where MyDatabase is the name of the database.

Also see How to Check a Database’s Compatibility Level in SQL Server.