NULL Equals NULL with NULLSEQUAL

[Deleted User]

Jim Knicely authored this tip.

The Vertica CASE expression is a generic conditional expression that can be used wherever an expression is valid. It is similar to case and if/then/else statements in other languages.

Example:

dbadmin=> SELECT CASE 1 WHEN 1 THEN 'It is 1' ELSE 'It is not 1' END;
  case
---------
It is 1
(1 row)

What if we are comparing a NULL with a NULL? Remember that in Vertica, a NULL can never equal anything.

dbadmin=> SELECT CASE NULL WHEN NULL THEN 'It is NULL' ELSE 'It is not NULL' END;
      case
----------------
It is not NULL
(1 row)

But in the CASE expression above, we do want NULL to equal NULL. Let’s try adding an “IS NULL”:

dbadmin=> SELECT CASE NULL WHEN IS NULL THEN 'It is NULL' ELSE 'It is not NULL' END;
ERROR 4856:  Syntax error at or near "NULL" at character 26
LINE 1: SELECT CASE NULL WHEN IS NULL THEN 'It is NULL' ELSE 'It is ...

That didn’t work! But we can use the NULLSEQUAL keyword so that NULL will equal NULL:

dbadmin=> SELECT CASE NULL WHEN NULLSEQUAL NULL THEN 'It is NULL' ELSE 'It is not NULL' END;
    case
------------
It is NULL
(1 row)

Have fun!