sql default value on join

Question

I have a sql query taking everything from one table (sdrcraw) and populating it in a 2nd table. The 2nd table has an additional column called 'local' and this gets populated with a join on country from the localelookup table.

This works as it should. I would like to add a default value in the event that a country is missing from the locallookup table. If the join returns a null then set that null local to 'AA' as an example.

sdrcraw.BIGuid, 
sdrcraw.SubsciberKey, 
sdrcraw.FIrstName, 
sdrcraw.Accountnumber, 
sdrcraw.Country, 
sdrcraw.MobileNo, 
localelookup.locale
FROM sdrcraw
LEFT JOIN [localelookup]
ON sdrcraw.country = localelookup.country

Answer 1

You could use a case statement as following:

SELECT
    sdrcraw.BIGuid,
    sdrcraw.SubsciberKey,
    sdrcraw.FIrstName,
    sdrcraw.Accountnumber,
    sdrcraw.Country,
    sdrcraw.MobileNo,
    CASE
        WHEN localelookup.locale IS NULL THEN 'AA'
        ELSE localelookup.locale
    END AS locale
FROM
    sdrcraw
    LEFT JOIN [localelookup] ON sdrcraw.country = localelookup.country

Answer 2

@Lyslexic - Should not the CASE logic be like this (see below)--basically, you are missing WHEN after CASE keyword:

CASE WHEN localelookup.locale IS NULL THEN 'AA' ELSE localelookup.locale END AS locale