You cannot make relational comparisons of ARRAY values. You cannot use any of the relational comparison operators on ARRAY data. You can only make relational comparisons on individual elements of an ARRAY.
Examples: Querying a Table using ARRAY_COMPARE
In this example, the inventory table contains two ARRAY columns, sale_price_june and sale_price_july, which contain the following element values:
sale_price_june[1:10] = 50, 100, 200, 90, 250, 550, 200, 200, 50, 75 sale_price_july[1:10] = 50, 100, 200, 90, 250, 550, 200, 200, 50, 75
The output of the following query is 1 since all of the element values in the sale_price_june array are equal to the element values in the sale_price_july array.
SELECT ARRAY_COMPARE(sale_price_june, sale_price_july) FROM inventory;
The following is the same query using method-style syntax.
SELECT sale_price_june.ARRAY_COMPARE(sale_price_july) FROM inventory;
Consider the same arrays but with different element values as follows:
sale_price_june[1:10] = 50, 100, 200, 90, 250, 550, 200, 200, 50, 75 sale_price_july[1:10] = 50, 100, 200, 90, 300, 550, 200, 200, 50, 75
The output of the following query is 0 since the element value in position 5 differs in the two arrays.
SELECT ARRAY_COMPARE(sale_price_june, sale_price_july) FROM inventory;
The following query compares the values of elements in positions 6 through 10. The output of this query is 1 since the element values in the given positions of both arrays are equal.
SELECT ARRAY_COMPARE(sale_price_june, sale_price_july, 6, 10) FROM inventory;