NULL Equals NULL with NULLSEQUAL: Quick Tip

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!