SQL Replace NULL Values with Own text

Question

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.

Answer 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