Use 'default value' in table when NULL

I am building a config table and want one row to be the default value. For example, I have a list table of all my databases and a table called msg which has all my return values. In the msg table, I have one row that is 'alldbs' and I want that value returned if there isn't another row for the joined database.

So the output should be:

--LIST TABLE
dbname                                             createdate
-------------------------------------------------- -----------------------
master                                             2015-06-23 10:05:14.363
model                                              2015-06-23 10:05:14.363
msdb                                               2015-06-23 10:05:14.363
tempdb                                             2015-06-23 10:05:14.363
dummy                                              2015-06-23 10:05:14.363

--MSG Table
dbname                                             msgval
-------------------------------------------------- --------------------
alldbs                                             Message 1
dummy                                              Message 2

--DESIRED OUTPUT
dbname                                             msgval
-------------------------------------------------- --------------------
dummy                                              Message 2
master                                             Message 1
model                                              Message 1
msdb                                               Message 1
tempdb                                             Message 1

Now I can get the result, but the query is clunky and it seems like there should be a better way:

select
    a.dbname
    ,b.msgval
from
    list a
    join msg b on a.dbname = b.dbname
union all
select
    a.dbname
    ,b.msgval
from
    list a
    ,msg b
where
    a.dbname not in (select dbname from msg)
    and b.dbname = 'alldbs'

Is there a more efficient way to write this query?

SQLFi

Using another left join:

select
    a.dbname
    , coalesce(b.msgval, d.msgval) as msgval
from
    list a
    left join msg b on a.dbname = b.dbname
    left join msg d on  b.dbname is null
                    and d.dbname = 'alldbs' ;

or a cross join (this requires an 'alldbs' row to be present, otherwise it will return 0 results.):

select
    a.dbname
    , coalesce(b.msgval, d.msgval) as msgval
from
    msg d 
    cross join list a 
    left join msg b on a.dbname = b.dbname
where d.dbname = 'alldbs' ;

SQlfiddle

Both queries assume there is only one row with dbname = 'alldbs'. If not, use the apply version provided by AndriyM.

StackExchange Database Administrators Q#104898, answer score: 8