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

The expression = ANY (list of single elements) is rewritten as the array_contains operation during parsing, and the parentheses must contain only one expression:

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