Comparing tables using ANY/SOME/ALL

You learn how to use quantified comparison predicates (ANY/SOME/ALL) in non-correlated subqueries according to the SQL standard. SOME is any alias for ANY.

You can use one of the following operators with a comparison predicate:

ALL:

If the table is empty, or the comparison is true for every row in subquery table, the predicate is true for that predicand.
If the comparison is false for at least one row, the predicate is false.

SOME or ANY:

If the comparison is true for at least one row in the subquery table, the predicate is true for that predicand.
If the table is empty or the comparison is false for each row in subquery table, the predicate is false.

If the comparison is neither true nor false, the result is undefined.

For example, you run the following query to match any value in c2 of tbl equal to any value in c1 from the same tbl:

select c1 from tbl where c1 = ANY (select c2 from tbl);

You run the following query to match all values in c1 of tbl not equal to any value in c2 from the same tbl.

select c1 from tbl where c1 <> ALL (select c2 from tbl);