Help Center/ Data Warehouse Service/ SQL Syntax Reference/ SQL Syntax Reference (9.1.0.x)/ Functions and Operators/ Array Functions and Operators/ Array Operators
Updated on 2026-03-04 GMT+08:00
View PDF
Share

Array Operators

Array comparisons compare the array contents element-by-element, using the default B-tree comparison function for the element data type. In multidimensional arrays, the elements are accessed in row-major order. If the contents of two arrays are equal but the dimensionality is different, the first difference in the dimensionality information determines the sort order. Table 1 lists the array operators supported by DWS.

Table 1 Array operators

Category

Description

Operator

Example

Compare

Check whether two arrays are equal.

=

 
1
 
SELECT ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] AS RESULT;

Check whether two arrays are not equal.

<>

 
1
 
SELECT ARRAY[1,2,3] <> ARRAY[1,2,4] AS RESULT;

Check whether an array is smaller than another array.

<

 
1
 
SELECT ARRAY[1,2,3] < ARRAY[1,2,4] AS RESULT;

Check whether an array is greater than another array.

>

 
1
 
SELECT ARRAY[1,4,3] > ARRAY[1,2,4] AS RESULT;

Check whether an array is less than or equal to another array.

<=

 
1
 
SELECT ARRAY[1,2,3] <= ARRAY[1,2,3] AS RESULT;

Check whether an array is greater than or equal to another array.

>=

 
1
 
SELECT ARRAY[1,4,3] >= ARRAY[1,4,3] AS RESULT;

Include or overlap

Check whether an array contains another array.

@>

 
1
 
SELECT ARRAY[1,4,3] @> ARRAY[3,1] AS RESULT;

Check whether an array is included in another array.

<@

 
1
 
SELECT ARRAY[2,7] <@ ARRAY[1,7,4,2,6] AS RESULT;

Check whether an array overlaps with another array (shares common elements).

&&

 
1
 
SELECT ARRAY[1,4,3] && ARRAY[2,1] AS RESULT;

Connect

Perform array-to-array concatenation.

||

 
1
 
SELECT ARRAY[1,2,3] || ARRAY[4,5,6] AS RESULT;

Perform element-to-array concatenation.

||

 
1
 
SELECT 3 || ARRAY[4,5,6] AS RESULT;

Perform array-to-element concatenation.

||

 
1
 
SELECT ARRAY[4,5,6] || 7 AS RESULT;

=

Description: Specifies whether two arrays are equal.

Example:

1
2
3
4
5
 
SELECT ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] AS RESULT;
 result 
--------
 t
(1 row)

<>

Description: Specifies whether two arrays are not equal.

Example:

1
2
3
4
5
 
SELECT ARRAY[1,2,3] <> ARRAY[1,2,4] AS RESULT;
 result 
--------
 t
(1 row)

<

Description: Specifies whether an array is less than another.

Example:

1
2
3
4
5
 
SELECT ARRAY[1,2,3] < ARRAY[1,2,4] AS RESULT;
 result 
--------
 t
(1 row)

>

Description: Specifies whether an array is greater than another.

Example:

1
2
3
4
5
 
SELECT ARRAY[1,4,3] > ARRAY[1,2,4] AS RESULT;
 result 
--------
 t
(1 row)

<=

Description: Specifies whether an array is less than another.

Example:

1
2
3
4
5
 
SELECT ARRAY[1,2,3] <= ARRAY[1,2,3] AS RESULT;
 result 
--------
 t
(1 row)

>=

Description: Specifies whether an array is greater than or equal to another.

Example:

1
2
3
4
5
 
SELECT ARRAY[1,4,3] >= ARRAY[1,4,3] AS RESULT;
 result 
--------
 t
(1 row)

@>

Description: Specifies whether an array contains another.

Example:

1
2
3
4
5
 
SELECT ARRAY[1,4,3] @> ARRAY[3,1] AS RESULT;
 result 
--------
 t
(1 row)

<@

Description: Specifies whether an array is contained in another.

Example:

1
2
3
4
5
 
SELECT ARRAY[2,7] <@ ARRAY[1,7,4,2,6] AS RESULT;
 result 
--------
 t
(1 row)

&&

Description: Specifies whether an array overlaps another (have common elements).

Example:

1
2
3
4
5
 
SELECT ARRAY[1,4,3] && ARRAY[2,1] AS RESULT;
 result 
--------
 t
(1 row)

||

Description: Specifies array-to-array concatenation.

Example:

1
2
3
4
5
 
SELECT ARRAY[1,2,3] || ARRAY[4,5,6] AS RESULT;
    result     
---------------
 {1,2,3,4,5,6}
(1 row)

1
2
3
4
5
 
SELECT ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] AS RESULT;
          result           
---------------------------
 {{1,2,3},{4,5,6},{7,8,9}}
(1 row)

||

Description: Specifies element-to-array concatenation.

Example:

1
2
3
4
5
 
SELECT 3 || ARRAY[4,5,6] AS RESULT;
  result   
-----------
 {3,4,5,6}
(1 row)

||

Description: Specifies array-to-element concatenation.

Example:

1
2
3
4
5
 
SELECT ARRAY[4,5,6] || 7 AS RESULT;
  result   
-----------
 {4,5,6,7}
(1 row)
Parent Topic: Array Functions and Operators