Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

NULL Equals NULL with NULLSEQUAL

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!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.