Use 'default value' in table when NULL

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.


Another way (not as pretty looking to me after seeing @ypercube's suggestion, but still...):

SELECT
  l.dbname,
  x.msgval
FROM
  dbo.list AS l
  CROSS APPLY
    (
      SELECT TOP (1)
        m.msgval
      FROM
        dbo.msg AS m
      WHERE
        m.dbname IN (l.dbname, 'alldbs')
      ORDER BY
        CASE m.dbname WHEN 'alldbs' THEN 2 ELSE 1 END ASC
    ) AS x
;

Using a subselect to get the missing msg:

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