Where value in list?

In this video we will discuss, how to use a comma separated list of values in SQL WHERE clause. Consider this Employees table

I alreeady know the FirstNames of 3 employees and we want to retrieve their respective records. This is very straight forward. We include a simple WHERE clause and the query works as expected. If we execute the following query, we get 3 the employees as expected.

The following is the challenge. At compile time, we don't know the firstnames of the 3 employees, we only know them at runtime. So, what do we do? Well, create a variable to hold the list of FIRSTNAMES separated by a delimeter like a comma (,) for example. Finally pass this variable as a parameter. Basically the following are the 2 lines of SQL code that we want to be able 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'

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 2 parameters - The string that we want to split and the seprator. In our example, the seprator is a comma (,)

If you execute the above query, we get the following result as expected.

Summary: in this tutorial, you will learn how to use the PostgreSQL IN operator in the WHERE clause to check if a value matches any value in a list.

You use IN operator in the WHERE clause to check if a value matches any value in a list of values.

The syntax of the IN operator is as follows:

The IN operator returns true if the value matches any value in the list i.e., value1 , value2 , …

The list of values can be a list of literal values such as numbers, strings or a result of a SELECT statement like this:

The query inside the parentheses is called a subquery, which is a query nested inside another query. Note that you will learn more about the subquery in the subsequent tutorial

Suppose you want to know the rental information of customer id 1 and 2, you can use the IN operator in the WHERE clause as follows:

The following query uses the equal (=) and OR operators instead of the IN operator. It is equivalent to the query above:

The query that uses the IN operator is shorter and more readable than the query that uses equal (=) and OR operators. In addition, PostgreSQL executes the query with the IN operator much faster than the same query that uses a list of OR operators.

You can combine the IN operator with the NOT operator to select rows whose values do not match the values in the list.

For example, the following statement finds all rentals with the customer id is not 1 or 2.

Similar to the IN operator, you can use the not equal (<>) and AND operators to write the NOT IN operator:

This query returns the same output as above query that use the NOT IN operator.

The following query returns a list of customer ids from the rental table with the return date is 2005-05-27:

Because this query returns a list of values, you can use it as the input of the IN operator like this:

For more information on the subquery, check it out the subquery tutorial.

In this tutorial, you have learned how to use the PostgreSQL IN operator to check if a value matches any value in a list of values.