Understanding How to Work with PostgreSQL IN Clause
In PostgreSQL, you use the IN clause with the WHERE clause to filter the output by checking against a list of values, and the result is a Boolean value.
Here’s the syntax to use:
The given syntax takes the value and checks it against the value1, value2, and value_n. It then returns a Boolean if there is a match. The list of values to check against can be of any literal type including strings and integers. Besides, you can create a subquery such as a SELECT statement.
Let’s discuss the different ways to use the PostgreSQL IN operator.
1. Working with a Subquery
As we mentioned earlier, the value to check against can be a subquery that extracts the values using a query statement such as SELECT. This way, you can query a table to check the values of a given column. Let’s use the following table for our example:
Suppose we want to check all the customers whose “order_id” could be any of the values that are specified in the query section. We add the target value as the first part of our statement and then use the WHERE clause with the IN clause to create the query.
Here’s how our query appears:
PostgreSQL checks the “order_id” column in the “customers” table and return all the records whose “order_id” matches any of the ones that we specified in the brackets.
For the first instance, we’ used a case of an integer list of values. We can also check against the strings. Note that if all the strings that you specify don’t match the target value, nothing is returned. The following example contains some strings which are not present in our table. Such strings will be skipped and only those matching strings will be displayed in the following output:
2. Working with IN(SELECT)
Sometimes, manually specifying the list of values may not work. You can choose to use a SELECT statement to fetch a list of values from your table and use them to check against the target value. Adding the following table to our database, we can combine it with the previous “customers” table to make our subquery:
Let’s say we only want to display the records from the “orders” table if the target value (order_id) is in the “customers” table. Here, the “customers.order_id” column is our list of values and we check its values against those on the “orders.order_id” column.
For this case, only three entries match the search and that’s what we get as the output for our IN(SELECT) PostgreSQL clause.
3. Working with the NOT IN PostgreSQL Clause
Using the previous command, we can choose to display the other values that don’t match the target value. For that, we do the opposite of what the IN clause does. Hence, we negate the command to be NOT IN.
Here’s how we write our new command:
Notice that we get a different output than those we got in example 2. That’s because we are working with the NOT IN instead of the IN clause.
Ideally, you can add NOT whenever you want to negate the results that you get with the PostgreSQL IN clause. The applications are endless. When you want to check the values and see those matching quickly, the IN clause is your best friend.
Conclusion
The IN clause works with the WHERE clause to check a target value against a list of values. The IN clause returns a Boolean which confirms if the target value has a match in the list of values that you specified. You can specify the values as literals or use the SELECT statement to create a subquery to use. We’ve given three instances on how you can use the PostgreSQL IN clause. Hopefully, that gave you insights into understanding how to work with the PostgreSQL IN clause.
