Array elements are compared starting from the first element. When a difference is found, the comparison stops and the result of the comparison between the most recently compared elements is returned. If all of the elements compare equal, then the arrays are equal. The comparisons performed are equivalent to those performed on expressions that are not held in arrays. If one array is shorter than another, and all elements of the shorter array are equal to the same elements of the longer array, the shorter array is considered less than the longer array.

When comparing arrays, the arrays must hold values with union-compatible data types. Duplicate elimination and GROUP BY are also supported over array expressions. For example, with the following array comparison, the query returns 1:

SELECT IF ARRAY(3,4,5) > ARRAY(2,3,4) THEN 1 ELSE 0 ENDIF;

Row types can be compared, used in joins, duplicate elimination, and grouping. Consider two row types similar to the row expression sample above:

DECLARE test1 ROW(x INT, w ROW(y INT, z INT));
DECLARE test2 ROW(a INT, b ROW(c INT, d CHAR(3)));
SET test1 = ROW(3, ROW(6,7));
SET test2 = ROW(3, ROW(8,'7'));
SELECT (IF (test1 > test2) THEN 1 ELSE 0 ENDIF) AS RESULT FROM dummy;

Two row expressions can be compared only if their structures match. However, while the row expressions must have the same structure, the names of the attributes of a row type do not need to be identical, and the data types of the individual leaf values do not need to be identical—only union compatible.

All ROW comparisons other than equality and inequality operations result in UNKNOWN.