Performing Null check using HANA SQL Script

You can go for using either NULLIF or COALESCE function to serve your requirement for null checking in HANA SQL Script.

NULLIF Function

NULLIF (expression1, expression2): This function will return the same type whatever is specified as the first expression.

Basically, NULLIF returns ?

• The first expression if the two expressions are not equal.

• NULL of type of first expression if the expressions are equal

Example

Here's how NULLIF works in practice ?

SELECT NULLIF(10, 5) AS result1,     -- Returns 10 (not equal)
       NULLIF(5, 5) AS result2,      -- Returns NULL (equal)
       NULLIF('A', 'B') AS result3   -- Returns 'A' (not equal)
FROM DUMMY;

COALESCE Function

The other function available is COALESCE which basically checks if the first value provided is NULL then it will return the second value.

Example

Using COALESCE to handle NULL values ?

c = COALESCE(b, a)

If b is null then the function will return a otherwise b. So if you need to put a null check and use some default value in place of NULL then you can go ahead and use COALESCE.

SELECT COALESCE(NULL, 'Default') AS result1,    -- Returns 'Default'
       COALESCE('Value', 'Default') AS result2,  -- Returns 'Value'
       COALESCE(NULL, NULL, 'Final') AS result3  -- Returns 'Final'
FROM DUMMY;

Conclusion

Both NULLIF and COALESCE functions are essential for handling NULL values in HANA SQL Script, with NULLIF comparing expressions and COALESCE providing default values for NULL cases.