ANY, SOME, and ALL

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Applies a comparison operator between an expression and each element of an array. Returns a boolean result. SOME is a synonym for ANY.

1<expression> <operator> ANY ( <array> )
2<expression> <operator> SOME ( <array> )
3<expression> <operator> ALL ( <array> )

• <expression>: The left-hand value to compare against each array element.
• <operator>: A comparison operator that returns a boolean result.
• <array>: An array value, such as an array constructor (ARRAY[1, 2, 3]) or a cast array literal ('[1, 2, 3]'::int[]).

ANY (or its synonym SOME) returns true if the comparison holds for any element, and false if no element matches, including when the array is empty.

ALL returns true if the comparison holds for every element, including when the array is empty.

Returns NULL if no comparison succeeds and at least one element yields NULL.

• These operators are the non-subquery counterparts of the subquery forms ANY / SOME and ALL.

1SELECT 1 = ANY (ARRAY[1, 2, 3]) AS result;

Returns true.

1SELECT 1 = ALL (ARRAY[1, 2, 3]) AS result;

Returns false because not every element equals 1.

1SELECT 4 = ANY (ARRAY[]::int[]) AS any_result,
2       4 = ALL (ARRAY[]::int[]) AS all_result;

Returns false for any_result and true for all_result because an empty array has no element to contradict ALL.

• IN and NOT IN
• Subquery Comparisons
• Comparison Operators and Predicates