Select a default value if returned result is NULL in MySQL

Question

The following query runs fine, but I want to input the option that if the email returned is NULL, then I input "example@google.com" or something similar. I have read up on a few functions to do so, like COALESCE(EMAIL,"example@google.com"), but I am unsure on the placement of that function in the script. Can you please direct me as to where I should inject that function and if I am even going at this in the right direction? Thanks you.

SELECT LCASE(LOGIN_NAME) as uniqueid, CONCAT('sha-512:', PASSWD) as password, REPLACE(CONTACT_NAME, '"', '') as name, \
     CONCAT('unit,', \
       MAX(CASE WHEN USER_TYPE = 'custom' THEN  \
          'location_employee'  \
        WHEN (LOGIN_NAME != 'link' AND USER_TYPE = 'owner' AND (UA.PARTY_SITE_ID IS NULL OR NOT EXISTS (SELECT U2.ID FROM CUSTOMER_DATA.USER_ACCESS U2 WHERE U2.USER_ID=U.ID AND (U2.PARTY_SITE_ID IS NULL OR U2.PARTY_SITE_ID = '')) ) )THEN  \
          'master'  \
        ELSE  \
          'location'  \
        END)) AS role, \
     MAX(EMAIL) as email, \
     MAX(PHONE) as phone, \
     MAX(FAX) as fax \
     FROM ORACLE_EXPORTS.SHIP_TO ST, \
     CUSTOMER_DATA.USER U \
     JOIN CUSTOMER_DATA.USER_ACCESS UA ON U.ID = UA.USER_ID \
     WHERE ( \
     (UA.PARTY_SITE_ID IS NULL AND ST.CUSTOMER_ID = UA.CUSTOMER_ID ) \
     OR \
     (ST.PARTY_SITE_ID IS NOT NULL AND ST.PARTY_SITE_ID = UA.PARTY_SITE_ID ) \
     ) \
     AND U.LOGIN_NAME IN ( ? ) \
     GROUP BY LOGIN_NAME

score 39 · Answer 1 · answered May 01 '15 at 18:02

39

Use the MySQL's IFNULL function,

IFNULL(MAX(EMAIL), "example@google.com") as email

answered May 01 '15 at 18:02

konyak

10,818
4
59
65

2

This was failing to solve my problem until I realized that my blank field was acutally an **empty string** which is [different than NULL in MySQL](https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html). For empty strings this works: `IFNULL(NULLIF(EMAIL, ''), "NULL")`. – Brian Wood Dec 02 '20 at 19:25
2

@BrianWood, there is something poetic in nesting `NULLIF` inside an `IFNULL` :D For me though it also lowers the readability (makes me stop and wonder "what's this" for a few seconds). – lolotobg Aug 09 '21 at 16:06

score 18 · Answer 2 · answered Nov 07 '13 at 15:11

18

Well, where you try to get email ?

COALESCE(MAX(EMAIL), 'example@google.com') as email

answered Nov 07 '13 at 15:11

Raphaël Althaus

59,727
6
96
122

score 3 · Answer 3 · answered Nov 07 '13 at 15:11

3

SELECT coalesce(field_or_derived_value_that_could_be_null, "new value here"), ...
FROM ...
WHERE ...

answered Nov 07 '13 at 15:11

Marc B

356,200
43
426
500

Select a default value if returned result is NULL in MySQL

3 Answers3