NULL-handling functions

• 1: COALESCE
• 2: IFNULL
• 3: ISNULL
• 4: NULLIF
• 5: NULLIFZERO
• 6: NVL
• 7: NVL2
• 8: ZEROIFNULL

1 - COALESCE

Returns the value of the first non-null expression in the list. If all expressions evaluate to null, then COALESCE returns null.

COALESCE conforms to the ANSI SQL-92 standard.

Behavior type

Syntax

COALESCE ( { * | expression[,...] } )

Arguments

* | expression[,...]

One of the following:

• * (asterisk)

  Evaluates all columns in the queried table.

• expression

  An expression of any data type. Functions that are included in expression must be deterministic.

Examples

COALESCE returns the first non-null value in each row that is queried from table lead_vocalists. Note that in the first row, COALESCE returns an empty string.

=> SELECT quote_nullable(fname)fname, quote_nullable(lname)lname,
     quote_nullable(coalesce (fname, lname)) "1st non-null value" FROM lead_vocalists ORDER BY fname;
  fname  |   lname   | 1st non-null value
---------+-----------+--------------------
 ''      | 'Sting'   | ''
 'Diana' | 'Ross'    | 'Diana'
 'Grace' | 'Slick'   | 'Grace'
 'Mick'  | 'Jagger'  | 'Mick'
 'Steve' | 'Winwood' | 'Steve'
 NULL    | 'Cher'    | 'Cher'
(6 rows)

See also

• CASE expressions
• ISNULL

2 - IFNULL

Returns the value of the first non-null expression in the list.

IFNULL is an alias of NVL.

Behavior type

Syntax

IFNULL ( expression1 , expression2 );

Parameters

• If *expression1 *is null, then IFNULL returns expression2.

• If *expression1 *is not null, then IFNULL returns expression1.

Notes

• COALESCE is the more standard, more general function.

• IFNULL is equivalent to ISNULL.

• IFNULL is equivalent to COALESCE except that IFNULL is called with only two arguments.

• ISNULL(a,b) is different from x IS NULL.

• The arguments can have any data type supported by Vertica.

• Implementation is equivalent to the CASE expression. For example:

  CASE WHEN expression1 IS NULL THEN expression2
  ELSE expression1 END;
  

• The following statement returns the value 140:

  SELECT IFNULL(NULL, 140) FROM employee_dimension;
  

• The following statement returns the value 60:

  SELECT IFNULL(60, 90) FROM employee_dimension;
  

Examples

=> SELECT IFNULL (SCORE, 0.0) FROM TESTING;
 IFNULL
--------
  100.0
   87.0
     .0
     .0
     .0
(5 rows)

See also

• CASE expressions
• COALESCE
• NVL
• ISNULL

3 - ISNULL

Returns the value of the first non-null expression in the list.

ISNULL is an alias of NVL.

Behavior type

Syntax

ISNULL ( expression1 , expression2 );

Parameters

• If *expression1 *is null, then ISNULL returns expression2.

• If *expression1 *is not null, then ISNULL returns expression1.

Notes

• COALESCE is the more standard, more general function.

• ISNULL is equivalent to COALESCE except that ISNULL is called with only two arguments.

• ISNULL(a,b) is different from x IS NULL.

• The arguments can have any data type supported by Vertica.

• Implementation is equivalent to the CASE expression. For example:

  CASE WHEN expression1 IS NULL THEN expression2
  ELSE expression1 END;
  

• The following statement returns the value 140:

  SELECT ISNULL(NULL, 140) FROM employee_dimension;
  

• The following statement returns the value 60:

  SELECT ISNULL(60, 90) FROM employee_dimension;
  

Examples

SELECT product_description, product_price,
ISNULL(product_cost, 0.0) AS cost
FROM product_dimension;
      product_description       | product_price | cost
--------------------------------+---------------+------
 Brand #59957 wheat bread       |           405 |  207
 Brand #59052 blueberry muffins |           211 |  140
 Brand #59004 english muffins   |           399 |  240
 Brand #53222 wheat bread       |           323 |   94
 Brand #52951 croissants        |           367 |  121
 Brand #50658 croissants        |           100 |   94
 Brand #49398 white bread       |           318 |   25
 Brand #46099 wheat bread       |           242 |    3
 Brand #45283 wheat bread       |           111 |  105
 Brand #43503 jelly donuts      |           259 |   19
(10 rows)

See also

• CASE expressions
• COALESCE
• NVL

4 - NULLIF

Compares two expressions. If the expressions are not equal, the function returns the first expression (expression1). If the expressions are equal, the function returns null.

Behavior type

Syntax

NULLIF( expression1, expression2 )

Parameters

expression1

Is a value of any data type.

expression2

Must have the same data type as *expr1 *or a type that can be implicitly cast to match expression1. The result has the same type as expression1.

Examples

The following series of statements illustrates one simple use of the NULLIF function.

Creates a single-column table t and insert some values:

CREATE TABLE t (x TIMESTAMPTZ);
INSERT INTO t VALUES('2009-09-04 09:14:00-04');
INSERT INTO t VALUES('2010-09-04 09:14:00-04');

Issue a select statement:

SELECT x, NULLIF(x, '2009-09-04 09:14:00 EDT') FROM t;
           x            |         nullif
------------------------+------------------------
 2009-09-04 09:14:00-04 |
 2010-09-04 09:14:00-04 | 2010-09-04 09:14:00-04
SELECT NULLIF(1, 2);
 NULLIF
--------
      1
(1 row)
SELECT NULLIF(1, 1);
 NULLIF
--------
(1 row)
SELECT NULLIF(20.45, 50.80);
 NULLIF
--------
  20.45
(1 row)

5 - NULLIFZERO

Evaluates to NULL if the value in the column is 0.

Syntax

NULLIFZERO(expression)

Parameters

expression

(INTEGER, DOUBLE PRECISION, INTERVAL, or NUMERIC) Is the string to evaluate for 0 values.

Examples

The TESTING table below shows the test scores for 5 students. Note that test scores are missing for S. Robinson and K. Johnson (NULL values appear in the Score column.)

=> SELECT * FROM TESTING;
    Name     | Score
-------------+-------
 J. Doe      |   100
 R. Smith    |    87
 L. White    |     0
 S. Robinson |
 K. Johnson  |
(5 rows)

The SELECT statement below specifies that Vertica should return any 0 values in the Score column as Null. In the results, you can see that Vertica returns L. White's 0 score as Null.

=> SELECT Name, NULLIFZERO(Score) FROM TESTING;
    Name     | NULLIFZERO
-------------+------------
 J. Doe      |        100
 R. Smith    |         87
 L. White    |
 S. Robinson |
 K. Johnson  |
(5 rows)

6 - NVL

Returns the value of the first non-null expression in the list.

Behavior type

Syntax

NVL ( expression1 , expression2 );

Parameters

• If *expression1 *is null, then NVL returns expression2.

• If *expression1 *is not null, then NVL returns expression1.

Notes

• COALESCE is the more standard, more general function.

• NVL is equivalent to COALESCE except that NVL is called with only two arguments.

• The arguments can have any data type supported by Vertica.

• Implementation is equivalent to the CASE expression:

  CASE WHEN expression1 IS NULL THEN expression2
      ELSE expression1 END;
  

Examples

expression1 is not null, so NVL returns expression1:

SELECT NVL('fast', 'database');
nvl
------
 fast
(1 row)

expression1 is null, so NVL returns expression2:

SELECT NVL(null, 'database');
nvl
----------
 database
(1 row)

expression2 is null, so NVL returns expression1:

SELECT NVL('fast', null);
nvl
------
 fast
(1 row)

In the following example, expression1 (title) contains nulls, so NVL returns expression2 and substitutes 'Withheld' for the unknown values:

SELECT customer_name, NVL(title, 'Withheld') as title
FROM customer_dimension
ORDER BY title;
     customer_name      | title
------------------------+-------
 Alexander I. Lang      | Dr.
 Steve S. Harris        | Dr.
 Daniel R. King         | Dr.
 Luigi I. Sanchez       | Dr.
 Duncan U. Carcetti     | Dr.
 Meghan K. Li           | Dr.
 Laura B. Perkins       | Dr.
 Samantha V. Robinson   | Dr.
 Joseph P. Wilson       | Mr.
 Kevin R. Miller        | Mr.
 Lauren D. Nguyen       | Mrs.
 Emily E. Goldberg      | Mrs.
 Darlene K. Harris      | Ms.
 Meghan J. Farmer       | Ms.
 Bettercare             | Withheld
 Ameristar              | Withheld
 Initech                | Withheld
(17 rows)

See also

• CASE expressions
• COALESCE
• ISNULL
• NVL2

7 - NVL2

Takes three arguments. If the first argument is not NULL, it returns the second argument, otherwise it returns the third argument. The data types of the second and third arguments are implicitly cast to a common type if they don't agree, similar to COALESCE.

Behavior type

Syntax

NVL2 ( expression1 , expression2 , expression3 );

Parameters

• If expression1 is not null, then NVL2 returns expression2.

• If expression1 is null, then NVL2 returns expression3.

Notes

Arguments two and three can have any data type supported by Vertica.

Implementation is equivalent to the CASE expression:

CASE WHEN expression1 IS NOT NULL THEN expression2 ELSE expression3 END;

Examples

In this example, expression1 is not null, so NVL2 returns expression2:

SELECT NVL2('very', 'fast', 'database');
nvl2
------
 fast
(1 row)

In this example, expression1 is null, so NVL2 returns expression3:

SELECT NVL2(null, 'fast', 'database');
nvl2
----------
 database
(1 row)

In the following example, expression1 (title) contains nulls, so NVL2 returns expression3 ('Withheld') and also substitutes the non-null values with the expression 'Known':

SELECT customer_name, NVL2(title, 'Known', 'Withheld')
as title
FROM customer_dimension
ORDER BY title;
     customer_name      | title
------------------------+-------
 Alexander I. Lang      | Known
 Steve S. Harris        | Known
 Daniel R. King         | Known
 Luigi I. Sanchez       | Known
 Duncan U. Carcetti     | Known
 Meghan K. Li           | Known
 Laura B. Perkins       | Known
 Samantha V. Robinson   | Known
 Joseph P. Wilson       | Known
 Kevin R. Miller        | Known
 Lauren D. Nguyen       | Known
 Emily E. Goldberg      | Known
 Darlene K. Harris      | Known
 Meghan J. Farmer       | Known
 Bettercare             | Withheld
 Ameristar              | Withheld
 Initech                | Withheld
(17 rows)

See also

• CASE expressions
• COALESCE
• COALESCE

8 - ZEROIFNULL

Evaluates to 0 if the column is NULL.

Syntax

ZEROIFNULL(expression)

Parameters

expression

String to evaluate for NULL values, one of the following data types:

• INTEGER

• DOUBLE PRECISION

• INTERVAL

• NUMERIC

Examples

The following query returns scores for five students from table test_results, where Score is set to 0 for L. White, and null for S. Robinson and K. Johnson:

=> SELECT Name, Score FROM test_results;
    Name     | Score
-------------+-------
 J. Doe      |   100
 R. Smith    |    87
 L. White    |     0
 S. Robinson |
 K. Johnson  |
(5 rows)

The next query invokes ZEROIFNULL on column Score, so Vertica returns 0 for for S. Robinson and K. Johnson:

=> SELECT Name, ZEROIFNULL (Score) FROM test_results;
    Name     | ZEROIFNULL
-------------+------------
 J. Doe      |        100
 R. Smith    |         87
 L. White    |          0
 S. Robinson |          0
 K. Johnson  |          0
(5 rows)

You can also use ZEROIFNULL in PARTITION BY expressions, which must always resolve to a non-null value. For example:

CREATE TABLE t1 (a int, b int) PARTITION BY (ZEROIFNULL(a));
CREATE TABLE

Vertica invokes this function when it partitions table t1, typically during a load operation. During the load, the function checks the data of the PARTITION BY expression—in this case, column a—for null values. If encounters a null value in a given row, it sets the partition key to 0, instead of returning with an error.