CODEWITHSUNDEEP
mysqlcoalesce
Grateful
Grateful

Reputation: 10175

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?

Upvotes: 1

Views: 1735

Answers (1)

Jacob Lambert
Jacob Lambert

Reputation: 7679

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 ''.

Upvotes: 3

Related Questions