The ARRAY_COMPARE function does a pairwise comparison of the two array arguments, and returns a scalar value indicating whether or not the two array expressions contain all of the same element values.
ARRAY_COMPARE follows the rules for ANSI array comparison as follows:
- If either expr1 or expr2 evaluates to NULL, the result is NULL.
- expr1 and expr2 are equal if and only if all the element values of expr1 are equal to the element values of expr2.
- An element value of expr1 is equal to an element value of expr2 if and only if they both have the same value and both have the same position in the array.
- If any non-NULL elements do not match for a given element of Array A and Array B, then this is considered a mismatch and ARRAY_COMPARE returns a value of 0.
- If either or both values are NULL for a given element, and all other elements in the arrays match, this is considered as a mismatch according to the ANSI standard. The result in this case, according to the ANSI standard, is UNKNOWN. In Teradata, this evaluates to a NULL result.
If rule (1) or (5) is satisfied, ARRAY_COMPARE returns NULL. If rules (2) and (3) are satisfied, ARRAY_COMPARE returns 1. Otherwise, ARRAY_COMPARE returns 0.
When the optional nulls_equal_flag is set to 1, then when both values are NULL for a given element, this is considered as a match, which is a deviation from the ANSI standard. If all other elements match, then rule (2) applies.
If you specify the optional argument scope_reference, then the function is applied only to the elements within the limits of the given scope.
If an array argument contains any elements that are in an uninitialized state, an error is returned. Use a scope reference to avoid referencing a range of the ARRAY that contains uninitialized elements, or set any uninitialized elements to NULL. You can do this with the OEXTEND method. See OEXTEND.