Comparison conditions

A comparison condition is used to compare one expression with another, and the comparison result can be TRUE, FALSE, or UNKNOWN.

The following table describes the comparison conditions in detail.

Simple comparison conditions

The syntax for a simple comparison condition is as follows:

expr {= | != | <> | < | > | <= | >= | <=>} expr

Group comparison conditions

You can use ANY, SOME, and ALL to compare an expression with the results of a subquery.

Comparison with subqueries

expr {= | != | <> | < | > | <= | >=} { ANY | SOME | ALL } ( subquery )

ANY is equivalent to SOME. Example:

obclient> SELECT * FROM emp WHERE salary = ANY (SELECT salary FROM emp WHERE deptno = 30) ORDER BY empno;
obclient> SELECT * FROM emp WHERE salary > ALL (SELECT salary FROM emp WHERE deptno = 30) ORDER BY empno;

Comparison with single-element lists

= ANY (single-element list) is rewritten as an array_contains operation during parsing, and the parentheses must contain only one expression:

-- Equivalent to the semantics of array_contains
SELECT * FROM emp WHERE id = ANY ((100));