Differences Between the IN and ANY Operators in PostgreSQL

1. the IN Operator in PostgreSQL
2. the ANY Operator in PostgreSQL
3. Differences Between the IN and ANY Operators in PostgreSQL
4. Conclusion

Today, we will be looking at the working of the IN and the ANY operators in detail in PostgreSQL. However, remember that both are standard SQL operators, meaning that we can use them across several management platforms.

By looking at them one by one and working through some examples, we will be able to understand a great deal about them.

the IN Operator in PostgreSQL

You will find IN and ANY under the Sub-Query Expressions listed in the PostgreSQL documentation.

Syntax:

expression IN (subquery)

The subquery above is a query written to return a result, in our case, one column exactly. Why? Because distinct columns contain respective sets of data.

We want to find the expression in this query, so we use the operator IN. If the expression matches any of the rows under the column returned by the subquery, the IN operator will return TRUE; otherwise, FALSE.

If the expression and the subquery tend to be NULL, the IN operator will return NULL.

Now let’s assume that we don’t have an expression but a set of expressions, meaning an entire row that we need to see if it exists or not. In that case, the IN operator also has a syntax for evaluation:

row_constructor IN (subquery)

The row_constructor builds a valid row from a set of values, such as in the following query:

SELECT ROW('cat', 'adam');

A row is created with two values under separate unclassified columns. Thus, it helps build rows from any number of values provided to its constructor.

Now back to our original statement.

The subquery in this statement will return a set of rows with multiple columns rather than a set of rows with a single column. As of now, there can be a whole row to be compared with the rows returned.

In this scenario, it is better to use this syntax for evaluation. If an entire row matches, this will return TRUE and else, FALSE.

Similarly, you can apply the NOT IN operator, which will return the exact opposite results.

Now, let’s proceed to an example. First, we’ll create a table cat with two columns: ID and NAME.

CREATE TABLE cat (
    ID int PRIMARY KEY,
    NAME TEXT
)

We will go ahead and insert two values into our table.

INSERT INTO cat VALUES(1, 'Adam'), (2, 'Jake')

And let’s find out if our table has the name Adam.

Select 'Adam' in (Select Name from cat)

Output:

Now, how about searching an entire row? Let’s go ahead and use our row constructor for that.

Now, we want to find Adam but with an ID this time and compare it to the NAME column and the entire rows returned from the cat table.

select ROW(1, 'Adam') in (SELECT * from cat)

It will also return a TABLE with a TRUE value.

Now that you have learned the primary usage of the IN operator, let’s go ahead and understand how ANY works.

the ANY Operator in PostgreSQL

The ANY operator is the same as the IN operator. ANY also returns TRUE if a row matches and FALSE if it doesn’t.

It uses the ANY/SOME keyword, similar to the IN operator. To use the ANY operator, you can go ahead and write something like this for our table above:

Select 'Adam' = any (Select Name from cat)

So, we have to use an operator here, and the ANY operator returns TRUE since Adam exists. For the row constructor, use the following.

Select ROW(1, 'Adam' ) = any (Select * from cat)

One of the differences we can list here is using another operator in the ANY/SOME syntax. If you want to see if something equals the rows returned and wish to return TRUE, use the query syntax given above.

But if, for example, you have to search the value John in the table and return a TRUE still, you can go ahead and write the following:

Select 'John' != any (Select Name from cat)

It will return TRUE as there is no cat names John.

Aside from this, various other differences are worth mentioning.

Differences Between the IN and ANY Operators in PostgreSQL

There are two syntax variants of IN and ANY in PostgreSQL. We read up on the first syntax with the single value to be searched, but we slightly differ in the second variant of the row constructor.

Variant Differences for Row Searching

In our table are the values Adam and Jake. We want to use these in the WHERE statement to search.

We could write it as shown below.

SELECT * from cat
WHERE (ID, NAME) = any(ARRAY[(1, 'Adam'), (2, 'Jake')])

It would help find all the values in the cat table, with the array having our custom values for searching.

Suppose we had the following configuration:

-----
(1, 'Adam')
(1, 'John')
(2, 'Marta')
(2, 'Mack')
(2, 'Kitty')
(3, 'Mars')

-----

To see if all of the rows in the above table had some similar rows in our cat table and then return those rows. We would be better off using the array inside the ANY operator, which would help us operate and query all these values.

Of course, this is mainly used as a function and cannot be called by JOIN, making ANY more effective.

Various Modifications to the ANY Operator

You can also use the LIKE, WHERE, or such operators with ANY. So, you can say that:

SELECT 'foo' LIKE ANY('{FOO,bar,%oo%}');

It will compare Adam to any of the words given in the array. It is not LIKE FOO, but LIKE %oo%, so the query returns a TRUE.

Conclusion

Today, you learned how different the IN is from the ANY operator.

Even though both are similar, the ANY operator is vaster and better than IN. Due to its applications, it can save both space and time (to a lesser extent still) in major coding programs.