Chris So
Jun 8, 2021

Hi Steve, yes there is.

IFNULL() takes only one argument as a NULL-replacement.

COALESCE() takes a number of arguments, evaluates them in order and replaces the NULL with the first non-NULL value it can find.

Example:

COALESCE(field_with_nulls, value_1, value_2, value_3).

If the initial field is NULL, then the function evaluates value_1. If that is not NULL, it returns the corresponding value of value_1. If it is NULL, it jumps to value_2. Here the same routine is applied. So, if not NULL, return corresponding value of value_2 - if NULL, jump to value_3. Now, if value_3 is NULL (i.e. all fields in this function are NULL) COALESCE() will finally return NULL.

Hope this helps. :)

Chris So
Chris So

Written by Chris So

Data Dude and SQL enthusiast. Occasionally does Pixel art◾

Responses (1)