Hey guys, I'm Venkat and in this video we'll discuss,
how to use a comma separated list of values in SQL WHERE clause. Consider this Employees table.
I already know the first names of three employees and we want to retrieve their respective records.
This is very straightforward, we include a simple WHERE clause and the query works as expected.
Now, the following is the challenge. At compile time, we don't know the first names of the three
employees, we only know them at run time. So, what do we do? Well, create a variable
to hold the list of first names separated by a delimiter like a comma for example. Finally,
pass this variable as a parameter, basically the following are the two lines of SQL code
that we want to be able to execute. This sounds so simple and easy, but trust me, it's not. The
first time when someone asked me this question, it took over 30 minutes to figure out what's going on
and to get it right. If we execute the above query as it is, we get an empty result set.
This is because we do not have any employee in the Employees table whose FirstName='Mark,John,Sara'.
Here is the SQL script that creates and seeds Employees table with test data. If you need
the script, I'll have it available on my blog and include the link in the description of this video.
This query is straightforward. We know the employee first names at compile time, so when we
execute it, we get the result as expected. The problem is with this query. We have a variable
with employee first names separated by comma. When we execute this query, we get no rows,
why? Well, because in the Employees table, we don't have any employee record whose FirstName
is 'Mark,John,Sara'. The easiest way to get this to work is by using STRING_SPLIT() SQL Server
built-in function. This function is very easy to use. As the name implies, it splits a given string
and returns a single column table whose rows are the substrings. The name of the column is "value".
It has two parameters - the string that we want to split and the separator. In our example,
the separator is a comma. Now, consider this query. This variable FirstNames contains our
string that we want to split. So, we are passing both the parameters here - the string that we
want to split and our separator, in our case the separator is comma. So, let's execute this query.
There we go. We have the three sub strings in a single column table as rows and notice the
name of the column, it is "value". Now, we know what to do. Use these values in the WHERE clause.
So, "Select * from Employees Where FirstName in whatever our STRING_SPLIT() function returns".
Now, we can either use * here or we can use the name of the column. We know the name of the
column is "Value". Actually, to make this query more readable, let's use the name of the column.
There we go, we have our result as expected. At the moment,
in our query we are using SQL server IN clause and keep in mind the STRING_SPLIT() function
returns a single column table. This means we can use the result set returned by this
function in a join if we want to and in general for performance, joins are usually better than
the IN clause. So, we can replace this query with a join like this. Notice,
we are joining our "Employees" table with the result set returned by STRING_SPLIT() function
and in the result set that this function returns, we have only one column "Value". So, we are
joining that with the "FirstName" column in the "Employees" table. So, if we execute this query,
we have the same result as before. That's it in this video. Thank you for listening.