Help Center/ MapReduce Service/ Component Operation Guide (LTS)/ Using HetuEngine/ HetuEngine SQL Syntax/ SQL Functions and Operators/ Condition Expression
Updated on 2022-11-18 GMT+08:00
View PDF

Condition Expression

CASE

Standard SQL CASE expressions have two modes.

  • In simple mode, search for each value of the expression from left to right until the same expression is found.

    CASE expression

    WHEN value THEN result

    [ WHEN ... ]

    [ ELSE result ]

    END

    Returns the result that matches the value. If no value is matched, the result of the ELSE clause is returned. If there is no ELSE clause, null is returned. Example: 
    select a,
case a
 when 1 then 'one'
 when 2 then 'two'
 else 'many' end from
 (values (1),(2),(3),(4)) as t(a);
 a | _col1 
---|-------
 1 | one   
 2 | two   
 3 | many  
 4 | many  
(4 rows)
  • In search mode, the system checks the Boolean value of each condition from left to right until the value is true and returns the matching result.

    CASE

    WHEN condition THEN result

    [ WHEN ... ]

    [ ELSE result ] END

    If none of the conditions is met, the result of the ELSE clause is returned. If there is no ELSE clause, null is returned. Example: 
    select a,b,
case
when a=1 then 'one'
when b=2 then 'tow'
else 'many' end from (values (1,2),(3,4),(1,3),(4,2)) as t(a,b);
 a | b | _col2 
---|---|-------
 1 | 2 | one   
 3 | 4 | many  
 1 | 3 | one   
 4 | 2 | tow   
(4 rows)

IF

The IF function is a language structure. It has the same function as the following CASE expression:

CASE

WHEN condition THEN true_value

[ ELSE false_value ] END

  • if(condition, true_value)

    If condition is true, true_value is returned. Otherwise, null is returned and true_value is not calculated.

    select if(a=1,8) from (values (1),(1),(2)) as t(a); -- 8 8 NULL
select if(a=1,'value') from (values (1),(1),(2)) as t(a); -- value value NULL
  • if(condition, true_value, false_value)

    If condition is true, true_value is returned. Otherwise, false_value is returned.

    select if(a=1,'on','off') from (values (1),(1),(2)) as t(a); 
_col0 
-------
 on    
 on    
 off   
(3 rows)

COALESCE

coalesce(value[, ...])

Returns the first non-null value in the parameter list. Similar to CASE expressions, parameters are calculated only when necessary.

It is similar to the nvl function of MySQL and is often used to convert a null value to 0 or ' ' (null character).

 select coalesce(a,0) from (values (2),(3),(null)) as t(a); -- 2 3 0

NULLIF

  • nullif(value1, value2)

    If value1 is equal to value2, null is returned. Otherwise, value1 is returned.

    select nullif(a,b) from (values (1,1),(1,2)) as t(a,b); --
 _col0 
-------
  NULL 
     1 
(2 rows)
  • ZEROIFNULL(value)

    If the value is null, 0 is returned. Otherwise, the original value is returned. Currently, the varchar type is also supported.

    select zeroifnull(a),zeroifnull(b),zeroifnull(c) from (values (null,13.11,bigint '157'),(88,null,bigint '188'),(55,14.11,null)) as t(a,b,c);
 _col0 | _col1 | _col2 
-------|-------|-------
     0 | 13.11 |   157 
    88 |  0.00 |   188 
    55 | 14.11 |     0 
(3 rows)
  • NVL(value1,value2)

    If value1 is null, value2 is returned. Otherwise, value1 is returned.

    select nvl(NULL,3);  -- 3
select nvl(2,3);    --2
  • ISNULL(value)

    If value1 is null, true is returned. Otherwise, false is returned.

    Create table nulltest(col1 int,col2 int);
insert into nulltest values(null,3);
select isnull(col1),isnull(col2) from nulltest;
 _col0 | _col1 
-------|-------
 true  | false 
(1 row)
  • ISNOTNULL(value)
    If value1 is null, false is returned. Otherwise, true is returned. 
    select isnotnull(col1),isnotnull(col2) from nulltest;
 _col0 | _col1 
-------|-------
 false | true  
(1 row)

TRY

Evaluates an expression. If an error occurs, Null is returned. It is similar to try catch in the programming language. The TRY function is generally used together with COALESCE. COALESCE can convert an abnormal null value to 0 or null. The following situations will be captured by the TRY function:

  • The denominator is 0.
  • The cast operation or function input parameter is incorrect.
  • The number exceeds the defined length.

This method is not recommended. Specify the preceding exceptions and preprocess data.

Example:

Assume that the origin_zip field in the following table contains invalid data:

--Create a table.
create  table shipping (origin_state varchar,origin_zip varchar,packages int ,total_cost int);

--Insert data.
insert into shipping 
values 
('California','94131',25,100),
('California','P332a',5,72),
('California','94025',0,155),
('New Jersey','08544',225,490);

--Query data.
SELECT * FROM shipping;
 origin_state | origin_zip | packages | total_cost
 --------------+------------+----------+------------ 
 California   |      94131 |       25 |        100 
 California   |      P332a |        5 |         72
 California   |      94025 |        0 |        155 
 New Jersey   |      08544 |      225 |        490 
(4 rows)

The query fails when TRY is not used:

SELECT CAST(origin_zip AS BIGINT) FROM shipping;
Query failed: Cannot cast 'P332a' to BIGINT
When TRY is used, null is returned: 
SELECT TRY(CAST(origin_zip AS BIGINT)) FROM shipping;
 origin_zip
 ------------  
     94131 
     NULL
     94025
     08544
 (4 rows)

The query fails when TRY is not used:

SELECT total_cost/packages AS per_package FROM shipping;
Query failed: Division by zero

The default values are returned when TRY and COALESCE are used.

SELECT COALESCE(TRY(total_cost/packages),0) AS per_package FROM shipping;  
 per_package
 -------------
    4  
    14     
    0      
    19
 (4 rows)