'SQL Replace NULL Values with Own text

I need to display the keyboard players from a list of bands, and I've been able to using the following SQL:

SELECT BAND.NAME AS Band_Name, KBPLAYER.NAME AS Keyboard_Player
FROM BAND
FULL OUTER JOIN (
    SELECT M.NAME, MO.BID
    FROM MEMBEROF MO, MEMBER M
    WHERE MO.INSTRUMENT='keyboards'
    AND M.MID=MO.MID
    ) KBPLAYER
ON BAND.BID=KBPLAYER.BID
ORDER BY BAND.NAME, KBPLAYER.NAME

The above query displays the names of all the band and the keyboard player (if any) in that band, but I also want to display 'No KeyBoard Players' for those bands that don't have a keyboard player. How can I achieve this? Please let me know if you need me to furnish with details of the table structure.

Update: Please note that I'm not able to use any of the SQL3 procedures (COALESCE, CASE, IF..ELSE). It needs to conform strictly to SQL3 standard.

sqlpostgresql


Solution 1:[1]

Use the coalesce function. This function returns the first of it's arguments that are not null. Eg:

COALESCE(KBPLAYER.NAME,'No KeyBoard Players') AS Keyboard_Player

Solution 2:[2]

As per your comment to replace empty string to some text, the simple solution is to use case statement like below.

SELECT BAND.NAME AS Band_Name, 
CASE WHEN KBPLAYER.NAME = '' THEN 'No Player' ELSE KBPLAYER.NAME END AS Keyboard_Player
FROM BAND
FULL OUTER JOIN (
    SELECT M.NAME, MO.BID
    FROM MEMBEROF MO, MEMBER M
    WHERE MO.INSTRUMENT='keyboards'
    AND M.MID=MO.MID
    ) KBPLAYER
ON BAND.BID=KBPLAYER.BID
ORDER BY BAND.NAME, KBPLAYER.NAME

Solution 3:[3]

Try this....

    SELECT BAND.NAME AS Band_Name, 
ISNULL(NULLIF(KBPLAYER.NAME,''),'No KeyBoard Players') AS Keyboard_Player
    FROM BAND
    FULL OUTER JOIN (
        SELECT M.NAME, MO.BID
        FROM MEMBEROF MO, MEMBER M
        WHERE MO.INSTRUMENT='keyboards'
        AND M.MID=MO.MID
        ) KBPLAYER
    ON BAND.BID=KBPLAYER.BID
    ORDER BY BAND.NAME, KBPLAYER.NAME

Solution 4:[4]

The above solutions will not handle an empty string. This one will.

COALESCE(NULLIF(KBPLAYER.NAME,''),'No KeyBoard Players') AS Keyboard_Player

Solution 5:[5]

If NVL is allowed, below should work.

NVL(KBPLAYER.NAME, 'No KeyBoard Players')

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 harmic
Solution 2 Naveen
Solution 3 tarzanbappa
Solution 4 MacGyver
Solution 5 Ram Dwivedi

Related Questions