JIYIK CN >

INToday, we will take a closer look at how the and ANYoperators work in PostgreSQL . However, remember that both are standard SQL operators, which means we can use them on multiple management platforms.

By looking at them one by one and through some examples, we will be able to learn a lot about them.

INOperators in PostgreSQL

INYou'll find and under Subquery Expressions listed in the PostgreSQL documentation ANY.

grammar:

expression IN (subquery)

The subquery above is a query written to return results, in our case, exactly one column. Why? Because different columns contain their own data sets.

We want to find the expression in this query, so we use the operator . The operator will return TRUE INif the expression matches any row under the columns returned by the subquery ; otherwise, it returns FALSE.IN

If the expression and the subquery tend to be NULL, INthe operator returns NULL.

Now let's assume that we don't have a single expression, but a set of expressions, meaning we have a whole line where we need to see if it exists. In this case, INthe operator also has an evaluation syntax:

row_constructor IN (subquery)

row_constructorConstructing a valid row from a set of values, such as in the following query:

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

Creates a row with two values ​​under separate unsorted columns. Thus, it helps in building rows from any number of values ​​provided to its constructor.

Now let's go back to our original statement.

The subquery in this statement returns a set of rows containing multiple columns instead of a set of rows containing a single column. So far, the entire row can be compared to the returned row.

In this case, it is better to use this syntax to evaluate. If the entire line matches, it returns TRUE, otherwise it returns FALSE.

Likewise, you can apply NOT INthe operator and it will return the exact opposite result.

Now, let's look at an example. First, we'll create a table with two columns cat: IDand NAME.

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

We will proceed by inserting two values ​​into our table.

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

Let's see if our table has a name Adam.

Select 'Adam' in (Select Name from cat)

Output:

Now, what about searching an entire row? Let's continue using our row constructor.

Now, we want to find Adamthe but this time with an ID and compare it to NAMEthe column as well as the entire row returned from the table.cat

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

It will also return a TABLE with TRUE values.

Now that you understand INthe main uses of the operator, let's move on to understanding ANYhow works.

ANYOperators in PostgreSQL

ANYThe operator INis the same as the operator. ANYIt also returns TRUE if the row matches, otherwise it returns FALSE.

It uses ANY/SOMEthe keyword, which is similar to INthe operator. To use ANYthe operator, you can continue to write the following for our table above:

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

So, we have to use an operator here, and ANYthe operator returns TRUE because Adamexists. For the row constructor, use the following.

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

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

But if, for example, you had to search for a value in a table Johnand still return TRUE, you could go ahead and write the following:

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

It will return TRUE because there is no cat name John.

Beyond that, there are various other differences worth mentioning.

INDifference between and ANYoperators in PostgreSQL

There are two syntax variations in PostgreSQL IN, and ANY. We read the first syntax using a single value to search for, but we saw a slight difference in the second variation of the row constructor.

Variant differences for row search

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

We can write it like this.

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

This will help in finding catall the values ​​in the table which has the custom value that we are using for searching.

Assume we have the following configuration:

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

-----

catCheck all the rows in the above table to see if there are some similar rows in our table and then return those rows. We'd better ANYuse arrays in the operator, which will help us operate and query all these values.

Of course, this is primarily used as a function and cannot be JOINcalled by , making ANYmore efficient.

ANYVarious modifications to operators

You can also use operators like LIKE, , WHEREor ANYwith . So you can say:

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

It Adamcompares with any of the words given in the array. It is not LIKE FOO,, but LIKE %oo%, so the query returns TRUE.

in conclusion

Today, you learned the difference between the INand ANYoperators.

Although the two are similar, ANYthe operator INis bigger and better than . Due to its applications, it can save space and, to a lesser extent, time in major coding programs.