CODEWITHSUNDEEP
mysql
Pia Wurtzbach
Pia Wurtzbach

Reputation: 94

How do I use COALESCE and JOIN at the same time and including NULL values in MySQL?

I'm having trouble using COALESCE and JOIN at the same time. My plan is:

Although my query returns its proper values, it seems like NULL values are ignored in this case.

venture_list table:

-------------------
| vid | name      |
-------------------
| 1   | Malaysia  |
-------------------
| 2   | Singapore |
-------------------

request_forms:

---------------------------------------------
| fid | username  | venture | venture_other |
---------------------------------------------
| 1   | jake.long | 2       |               |
---------------------------------------------
| 2   | xander.f  | 0       | Japan         |
---------------------------------------------

Expected Result

---------------
| venturename |
---------------
| Singapore   |
---------------
| Japan       |
---------------

Actual Result

---------------
| venturename |
---------------
| Singapore   |
---------------

Here's my query:

SELECT COALESCE(NULLIF(ventures.name, null), venture_other) AS venturename
FROM request_forms forms
JOIN venture_list ventures ON ventures.vid = forms.venture

I tried rearranging the column names, but didn't work.

Upvotes: 0

Views: 654

Answers (3)

JuveLeo1906
JuveLeo1906

Reputation: 161

You were pretty close but need to change the nullif a bit:

select coalesce(nullif(ventures.name,0), venture_other) as venturename
  from request_forms forms
  join venture_list ventures
    on ventures.vid = forms.venture;

Basically, you want to use nullif to null out ventures.name if it is 0..then the coalesce does the rest.

Upvotes: 0

Peter M
Peter M

Reputation: 1059

The problem is that MySQL default behaviour on a JOIN is an INNER JOIN. Obviously since there is no matching result for the second row in venture_list you only get 1 row back.

Try using LEFT JOIN THAT way the column ventures.name will result in NULL and thus venture_other will be used.

Upvotes: 1

JNevill
JNevill

Reputation: 50034

Your original use of NULLIF() here is peculiar. From the documentation:

Returns NULL if expr1 = expr2 is true, otherwise returns expr1.

So your statement said, "In the event that ventures.name is NULL then return NULL else return ventures.name". Which is superfluous since a NULL will already return a NULL because it's NULL.

Instead of the case, and properly using the NULLIF() you could have the NULLIF() return a NULL in the event that ventures.name is 0:

SELECT COALESCE(NULLIF(ventures.name, 0), venture_other) AS venturename
FROM request_forms forms
JOIN venture_list ventures ON ventures.vid = forms.venture

Upvotes: 0

Related Questions