Compare tables using ANY/SOME/ALL

Apache Hive supports quantified comparison predicates (ANY/SOME/ALL) in non-correlated subqueries according to the SQL standard. SOME is any alias for ANY.

Hive supports using 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 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);