Sequence Comparison Operators

Syntax

any_comparison_operator ::= "=any" | "!=any" | ">any" | ">=any" | "<any" | "<=any"

Semantics

Comparisons between two sequences is done via another set of operators: =any, !=any, >any, >=any, <any, <=any. These any operators have existential semantics: the result of an any operator on two input sequences S1 and S2 is true if and only if there is a pair of items i1 and i2, where i1 belongs to S1, i2 belongs to S2, and i1 and i2 compare true via the corresponding value comparison operator. Otherwise, if any of the input sequences contains NULL, the result is NULL. Otherwise, the result is false.

Example 6-43 Sequence Comparison Operator

Select the id, lastName and address for users who are connected with the user with id 3. Notice the use of [] after connections: it is an array filter step (see Array-Filter Step Expressions), which returns all the elements of the connections array as a sequence (it is unnesting the array).

SELECT id, lastName, address FROM users
WHERE connections[] =any 3;

Example 6-44 Sequence Comparison Operator

Select the id and lastName for users who are connected with any users having id greater than 100.

SELECT id, lastName FROM users
WHERE connections[] >any 100;

Example 6-45 Sequence Comparison Operator

Select the id of each user who is connected with a user having id greater than 10 and is also connected with a user having id less than 100.

SELECT id FROM users u
WHERE 10 <any u.connections[] 
    AND u.connections[] <any 100;

Notice that the above query is not the same as the query: "select the id of each user who is connected with a user having an id in the range between 10 and 100". In the first query, we are looking for some connection with id greater than 10 and another connection (which may or may not be the same as the 1st one) with id less than 100. In the second query we are looking for some connection whose id is between 10 and 100. To make the difference clear, consider a Users table with only 2 users (say with ids 200 and 500) having the following connections arrays respectively: [ 1, 3, 110, 120 ] and [1, 50, 130]. Both of these arrays satisfy the predicates in the first query, and as a result, both users will be selected. On the other hand, the second query will not select user 200, because the array [ 1, 3, 110, 120 ] does not contain any element in the range 10 to 100.

The second query can be written by a combination of an EXISTS operator and an array filtering step:

SELECT id FROM users u
WHERE EXISTS u.connections
    [10 < $element AND $element < 100];

and the first query, with the 2 <any operators, is equivalent to the following one:

SELECT id FROM users u
WHERE EXISTS u.connections[10 < $element] 
    AND EXISTS u.connections[$element < 100];

Example 6-46 Sequence Comparison Operator

Select the first and last name of all users who have a phone number with area code 650. Notice that although we could have used [] after phones in this query, it is not necessary to do so, because the phones array (if it is indeed an array) is unnested implicitly by the .area step that follows.

SELECT firstName, lastName FROM users u
WHERE u.address.phones.area =any 650;