Oracle: How do I grab a default value when a more specific value is null from within the same query?

Question

That title is brutal, but I don't know how else to put it.

I have a key value table tied to a user_id that stores user preferences throughout the site. If a user hasn't gone in and updated any of their settings, any time I ask for a key (say "FAVORITE_COLOR" it's going to be null, so I need to pull the default setting that I have tied to the default user, user_id = 0.

I was thinking along the lines of UNION'ing the user_id = 0 results to the bottom of the query, but that'd just lead to duplicates. I'm not sure if it's even possible, but what'd I'd love to be able to say something like:

SELECT val FROM k_v WHERE user_id = 123 AND k = 'FAVORITE_COLOR'
UNION IF val IS NULL
SELECT val FROM k_v WHERE user_id = 0 AND k = 'FAVORITE_COLOR';

Any good way to do this?

Edit: Thanks for all the help on this. If your use case is only grabbing a single value which is what this question is, then NVL from dual is exactly what you want, but if you're planning on returning multiple pairs in the same query, take a look at some of the other answers as they may actually make more sense for implementation.

I wound up going with ZeissS's suggestion below as it's simple, still one query, works with multiple k,v pairs and I don't have a problem doing the possible duplicate filtering client side.

Answer 1

Use this,

select
  nvl(
    (SELECT val FROM k_v WHERE user_id = 123 AND k = 'FAVORITE_COLOR'),
    (SELECT val FROM k_v WHERE user_id = 0 AND k = 'FAVORITE_COLOR')
  ) val 
from dual
;

From the Oracle docs,

NVL(expr1, expr2): NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

Answer 2

SELECT nvl(k.k,kd.k) as k, 
       nvl(k.val, kd.val) as val 
FROM (select * from k_v where user_id = 0) kd 
     full outer join 
     (select * from k_v where user_id = 123) k 
     on kd.k = k.k 
WHERE 'FAVORITE_COLOR' in (k.k,kd.k)

I suggest doing the full outer join because it will guarantee a result both if there is no default or if there is no user-specific result. If there's no possibility of the default being missing, the query could be simplified a little:

SELECT nvl(k_v.k,kd.k) as k, 
       nvl(k_v.val, kd.val) as val 
FROM k_v kd 
     left outer join k_v 
     on kd.k = k_v.k 
        and k_v.user_id = 123
WHERE kd.k = 'FAVORITE_COLOR'
  and kd.user_id = 0

Answer 3

You could probably left join and then select the most appropriate one:

SELECT
  NVL(best.val, fallback.val) as val
FROM
  (SELECT val FROM k_v WHERE user_id = 0 AND k = 'FAVORITE_COLOR') as fallback
  left outer join (SELECT val FROM k_v WHERE user_id = 123 AND k = 'FAVORITE_COLOR') as best on 1 = 1

Not sure of the oracle syntax but you could probably substitute the "left outer on 1 = 1" join for some sort of cross join.

Answer 4

I would do something like this:

SELECT k_v.v FROM k_v WHERE userid IN (:userid, 0) ORDER BY userid DESC

and only use the first row returned.