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

Condition Expressions

Data that meets the requirements specified by condition expressions is filtered during SQL statement execution.

Condition expressions include the following types:

  • CASE

    A CASE expression is a conditional expression. Figure 1 shows the syntax diagram.

    Figure 1 case::=

    A CASE clause can be used in a valid expression. condition is an expression that returns a value of Boolean type.

    • If the result is true, the result of the CASE expression is the required result.
    • If the result is false, the WHEN or ELSE clauses in the statement are processed in the same way.
    • If every WHEN condition is not true, the result of the expression is the result of the ELSE clause. If the ELSE clause is omitted and no match is found, the result is NULL.
    • Operations on XML data are supported.

    Example:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
    		 
    m_db=# CREATE SCHEMA tpcds;

m_db=# CREATE TABLE tpcds.case_when_t1(CW_COL1 INT);

m_db=# INSERT INTO tpcds.case_when_t1 VALUES (1), (2), (3);

m_db=# SELECT * FROM tpcds.case_when_t1;
cw_col1 
---------
 1
 2
 3
(3 rows)

m_db=# SELECT CW_COL1, CASE WHEN CW_COL1=1 THEN 'one' WHEN CW_COL1=2 THEN 'two' ELSE 'other' END FROM tpcds.case_when_t1 ORDER BY 1;
 cw_col1 | case  
---------+-------
       1 | one
       2 | two
       3 | other
(3 rows)

m_db=# DROP TABLE tpcds.case_when_t1;

m_db=# DROP SCHEMA tpcds;
  • DECODE

    Figure 2 shows the syntax of DECODE.

    Figure 2 decode::=

    Compare each compare(n) with base_expr. value(n) is returned if a compare(n) matches the base_expr expression. If they do not match, default is returned.

    Operations on XML data are supported.

    For details about the information and examples, see "SQL Reference > Functions and Operators > Condition Expression Functions" in Developer Guide.

    1
2
3
4
5
    		 
    m_db=# SELECT DECODE('A','A',1,'B',2,0);
 case 
------
    1
(1 row)
  • COALESCE

    Figure 3 shows the syntax of COALESCE.

    Figure 3 coalesce::=

    COALESCE returns the first NOT NULL value. If all the parameters are NULL, NULL is returned. It is often used to replace NULL with the default value. Like a CASE expression, COALESCE only evaluates the parameters that are needed to determine the result. That is, parameters to the right of the first non-null parameter are not evaluated.

    Operations on XML data are supported.

    Example

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
    		 
    m_db=# CREATE SCHEMA tpcds;
m_db=# CREATE TABLE tpcds.c_tabl(description varchar(10), short_description varchar(10), final_value varchar(10));

m_db=# INSERT INTO tpcds.c_tabl VALUES('abc', 'efg', '123');
m_db=# INSERT INTO tpcds.c_tabl VALUES(NULL, 'efg', '123');

m_db=# INSERT INTO tpcds.c_tabl VALUES(NULL, NULL, '123');

m_db=# SELECT description, short_description, final_value, COALESCE(description, short_description, final_value) FROM tpcds.c_tabl ORDER BY 1, 2, 3, 4;
 description | short_description | final_value| coalesce
-------------+-------------------+------------+----------
 abc         | efg               | 123        | abc
             | efg               | 123        | efg
             |                   | 123        | 123
(3 rows)

m_db=# DROP TABLE tpcds.c_tabl;
m_db=# DROP SCHEMA tpcds;

    If description is not NULL, the value of description is returned. Otherwise, parameter short_description is calculated. If short_description is not NULL, the value of short_description is returned. Otherwise, parameter final_value is calculated. If final_value is not NULL, the value of final_value is returned. Otherwise, none is returned.

    1
2
3
4
5
    		 
    m_db=# SELECT COALESCE(NULL,'Hello World');
   coalesce    
---------------
 Hello World
(1 row)
  • NULLIF

    Figure 4 shows the syntax of NULLIF.

    Figure 4 nullif::=

    Only if the value of value1 is equal to that of value2 can NULLIF return NULL. Otherwise, the value of value1 is returned.

    Operations on XML data are supported.

    Example

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
    		 
    m_db=# CREATE SCHEMA tpcds;
m_db=# CREATE TABLE tpcds.null_if_t1 (
    NI_VALUE1 VARCHAR(10),
    NI_VALUE2 VARCHAR(10)
);

m_db=# INSERT INTO tpcds.null_if_t1 VALUES('abc', 'abc');
m_db=# INSERT INTO tpcds.null_if_t1 VALUES('abc', 'efg');

m_db=# SELECT NI_VALUE1, NI_VALUE2, NULLIF(NI_VALUE1, NI_VALUE2) FROM tpcds.null_if_t1 ORDER BY 1, 2, 3;

 ni_value1 | ni_value2 | nullif 
-----------+-----------+--------
 abc       | abc       | 
 abc       | efg       | abc
(2 rows)
m_db=# DROP TABLE tpcds.null_if_t1;
m_db=# DROP SCHEMA tpcds;

    If the value of value1 is equal to that of value2, NULL is returned. Otherwise, the value of value1 is returned.

    1
2
3
4
5
    		 
    m_db=# SELECT NULLIF('Hello','Hello World');
 nullif 
--------
 Hello
(1 row)
  • GREATEST (maximum value) and LEAST (minimum value)

    Figure 5 shows the syntax of GREATEST.

    Figure 5 greatest::=

    Select the maximum value from any numerical expression list. Operations on XML data are supported.

    1
2
3
4
5
    		 
    m_db=# SELECT greatest(9000,155555,2.01);
 greatest 
----------
   155555
(1 row)
  • LEAST (minimum value)

    Figure 6 shows the syntax of LEAST.

    Figure 6 least::=

    Select the minimum value from any numerical expression list.

    Operations on XML data are supported.

    1
2
3
4
5
    		 
    m_db=# SELECT least(9000,2);
 least 
-------
     2
(1 row)

    For details about the information and examples, see "SQL Reference > Functions and Operators > Condition Expression Functions" in Developer Guide.

Parent topic: Expressions