Grateful Grateful - 15 days ago 8
MySQL Question

MySQL COALESCE with two arguments?

COALESCE
is an SQL function that returns the first non-NULL expression among its arguments. So in the following statement...

SELECT USER.user_id,
USER.firstname,
USER.lastname,
...
COALESCE(EMPLOYEE.title, '') title,
...
FROM USER


... it is basically saying that if
EMPLOYEE.title
is
NULL
, then return and use
''
instead. Is my understanding correct?

Answer

Let's say that EMPLOYEE.title equals 'CEO'. If we plug this into the COALESCE function, our query would look something like:

SELECT COALESCE('CEO', '') sub_sector;

If we ran it, we would get 'CEO'. Now let's say that EMPLOYEE.title is NULL. If we plug that into the COALESCE function, our query would look something like:

SELECT COALESCE(NULL, '') sub_sector;

If we run that, we will get '' since COALESCE returns the first non-null value in its argument list. Since the first value is NULL, it will then check the next value, '', which is not NULL, so it will return it.

In the case of your query, if the field EMPLOYEE.title has a NULL value, the COALESCE function will return ''.