What is the Difference between PostgreSQL ANY and IN Operators?

Photo by Mathyas Kurmann on Unsplash

There are many times that we want to query for a set of records that may possess one of a collection of values. Maybe we are looking for all orders made by a certain customer in a group of customers, or all products with one of a certain set of colors.

When we get to these queries we have two choices: brute force the query by stringing together OR statements, or use the correct ANY and IN operators. In the next two sections I want to walk through what these operators are used for and their differences.

How do we use the IN Operator?

When would we want to use the IN operator? Let’s say that we have serial numbers for three products. We want to find these products by searching our products table for WHERE the serial number is for each of these products.

A naive approach would look like this:

Figure 1. Naive approach to get all product records with one of these serial_numbers.

This works, but isn’t very pretty. We have a list of products’ serial numbers and we want to return the product records that are contained IN this list. This is where we can use the IN operator to simplify our query as follows:

Figure 2. Using IN to select records contained within our list.

With this query we don’t have to repeat multiple assertions strung together with OR operators. Instead, we can create a list and query for any records that have serial_numbers contained IN our list.

How do we use the ANY Operator

How about the ANY operator…when would we want to use this? The answer is the exact same cases for which we use IN. These two operators are synonyms for each other with only a slight variation in their syntax. Here is how we would do the last query where we used IN, but with the ANY operator:

Figure 3. Using the ANY operator to select records contained with our list.

Here we can see ANY has only the slight change of requiring an equal sign to be able to search for product records that have a serial number within our given list.

Conclusion

Good PostgreSQL queries are simple and clean queries. I hope you now understand use cases for the ANY and IN operators and the syntax differences between the two. Really, choosing ANY or IN comes down to personal preference. Either operator will help simplify your queries by reducing redundancy of duplicative expressions and make it easier for others to tell what values you are looking for. Let me know in the comments your favorite patterns for simple, clean and elegant PostgreSQL queries!

Notes

https://stackoverflow.com/questions/30263671/postgresql-in-vs-any/30263897#30263897