Help Center/ GaussDB/ M-Compatibility Developer Guide(Distributed)/ SQL Reference/ Expressions/ Row Expressions
Updated on 2025-10-23 GMT+08:00
View PDF
Share

Row Expressions

The row comparison allows operators including =, <>, <, <=, >, and >=. The <=> operator or a similar operator is not supported.

The syntax is as follows:

row_constructor operator row_constructor
  • Both sides of the operator are row constructors. The values of both rows must have the same number of fields and they are compared with each other.
  • The supported data types are consistent with those of comparison operators.

<, <=, >, and >=

For operators <, <=, >, and >=, the columns in rows are compared from left to right until a pair of columns that are not equal or are empty are detected. If the pair of fields contains at least one null value, the comparison result is null. Otherwise, the comparison result of this pair of fields is the final result. If no unequal or empty column is found, the values in the two rows are equal. The final result is determined based on the operator meaning.

Example:

m_db=# SELECT (1,2,NULL) < (1,3,0) AS RESULT;
  result
----------
 t
(1 row)

m_db=# SELECT (4,5,6) > (3,2,1) AS RESULT;
 result 
--------
 t
(1 row)

m_db=# SELECT (4,1,1) > (3,2,1) AS RESULT;
 result 
--------
 t
(1 row)

m_db=# SELECT ('TEST','DATA') > ('DATA','DATA') AS RESULT;
 result 
--------
 t
(1 row)

m_db=# SELECT (4,1,1) > (3,2,NULL) AS RESULT;
 result 
--------
 t
(1 row)

m_db=# SELECT (NULL,1,1) > (3,2,1) AS RESULT;
 result 
--------

(1 row)

m_db=# SELECT (NULL,5,6) > (NULL,5,6) AS RESULT;
 result 
--------

(1 row)

m_db=# SELECT (4,5,6) > (4,5,6) AS RESULT;
 result 
--------
 f
(1 row)

m_db=# SELECT (2,2,5) >= (2,2,3) AS RESULT;
 result 
--------
 t
(1 row)

m_db=# SELECT (2,2,1) <= (2,2,3) AS RESULT;
 result 
--------
 t
(1 row)

= and <>

The use of operators = and <> is slightly different from other operators. If all columns in the two rows are not empty and meet the operator condition, the two rows meet the operator condition. If any column in the two rows is not empty and does not meet the operator condition, the two rows do not meet the operator condition. If any column in the two rows is empty, the comparison result is null.

Example:

m_db=# SELECT (1,2,3) = (1,2,3) AS RESULT;
 result 
--------
 t
(1 row)

m_db=# SELECT (1,2,3) <> (2,2,3) AS RESULT;
 result 
--------
 t
(1 row)

m_db=# SELECT (2,2,3) <> (2,2,NULL) AS RESULT;
 result 
--------

(1 row)

m_db=#  SELECT (NULL,5,6) <> (NULL,5,6) AS RESULT;
 result 
--------

(1 row)
Parent topic: Expressions