Suppose I have tables a (with column a1) and b (with columns b1 and b2) and I perform a left outer join
SELECT * FROM a LEFT OUTER JOIN b ON a.a1 = b.b1
Then b1 and b2 will be NULL where a value of a1 has no matching value of b1.
Can I provide a default value for b2, instead of NULL? Note that COALESCE won't work here, because I don't want the default value to override potential NULLs in b2 where there is a value of b1 matching a1.
That is, with a and b as
CREATE TABLE a (a1) AS VALUES (1), (2), (3) ; CREATE TABLE b (b1,b2) AS VALUES (1, 10), (3, null) ; a1 b1 | b2 --- -------- 1 1 | 10 2 3 | NULL 3
and a default for b2 of, say, 100, I want to get the result
a1 | b1 | b2 --------------- 1 | 1 | 10 2 | NULL | 100 3 | 3 | NULL
In this simple case I could do it "by hand" by looking at whether b1 is NULL in the output. Is that the best option in general, or is there a more standard and neater way?