Vertica Quick Tip: Handling NULL Equality in a WHERE Clause

[Deleted User]

Jim Knicely authored this post.

The predicate SQL element (i.e., the WHERE clause) is a truth-test. If the predicate test is true, it returns a value. Each predicate is evaluated per row, so that when the predicate is part of an entire table SELECT statement, the statement can return multiple results.

Sometimes you might want your predicate to return true if both sides of the equation is NULL. To accomplish this, I’ve seen folks use the NVL function on both sides of the equation to convert the NULL to the same value.

Example:

dbadmin=> SELECT 'I am true!' truth_test FROM dual WHERE NULL = NULL;
truth_test
------------
(0 rows)

dbadmin=> SELECT 'I am true!' truth_test FROM dual WHERE NVL(NULL, 'T') = NVL(NULL, 'T');
truth_test
------------
I am true!
(1 row)

But a better way is to use a single DECODE statement:

dbadmin=> SELECT 'I am true!' truth_test FROM dual WHERE DECODE(NULL, NULL, TRUE, FALSE);
truth_test
------------
I am true!
(1 row)

Why? Performance of course!

Example:

dbadmin=> \timing on
Timing is on.

dbadmin=> SELECT COUNT(*) FROM big_varchar_table_10_mil AS a JOIN big_varchar_table_10_mil AS b ON a.pk = b.pk WHERE NVL(a.the_varchar, '0') = NVL(b.the_varchar, '0');
  COUNT
----------
10000000
(1 row)

Time: First fetch (1 row): 79650.085 ms. All rows formatted: 79650.133 ms

dbadmin=> SELECT COUNT(*) FROM big_varchar_table_10_mil AS a JOIN big_varchar_table_10_mil AS b ON a.pk = b.pk WHERE DECODE(a.the_varchar, b.the_varchar, TRUE, FALSE);
  COUNT
----------
10000000
(1 row)

Time: First fetch (1 row): 6556.634 ms. All rows formatted: 6556.680 ms

Have fun!