Can I provide a default for a left outer join?

Question

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?

Answer 1

SELECT a.a1,b.b1,  
    CASE WHEN b.b1 is NULL THEN 5 ELSE b.b2 END AS b2  
FROM a LEFT OUTER JOIN b  
ON a.a1 = b.b1

Answer 2

I find COALESCE to be very useful in that case. It will return the first non NULL value from a list:

SELECT
 a.a1,
 b.b1,
 COALESCE (b.b2, 100) AS b2
FROM a
LEFT OUTER JOIN b
  ON (a.a1 = b.b1);

After @ypercube correctly identified an issue where my answer did not match the question. here is his correction:

SELECT 
  a.a1, 
  b.b1, 
  COALESCE(b.b2, d.b2) AS b2   
FROM a LEFT JOIN b ON a.a1 = b.b1 
LEFT JOIN (SELECT 100 AS b2) AS d ON b.b1 IS NULL;

Answer 3

The original answer to this question went unexplained, so let's give this another shot.

Using a CASE expression

Using this method we exploit that we have another value in a different column that IS NOT NULL in this case b.b1 if that value is null then we know the join failed.

SELECT
  a.a1,
  b.b1,  
  CASE WHEN b.b1 is NULL THEN 100 ELSE b.b2 END AS b2  
FROM a
LEFT OUTER JOIN b  
  ON (a.a1 = b.b1);

This will totally work, and generate the exact thing you want.

Using a sub-SELECT

Don't use this method, it's build-up idea. Keep reading.

If we do not have any NOT NULL columns that we can exploit like that, we need something to create a column that can function that way for us...

SELECT
  a.a1,
  b.b1,  
  CASE WHEN b.cond IS NULL THEN 100 ELSE b.b2 END AS b2  
FROM a
LEFT OUTER JOIN (
  SELECT true AS cond, b.*
  FROM b
) AS b
  ON (a.a1 = b.b1);

Using a row comparison

Even easier though then forcing a false value for which we can compare, is to compare the row. In PostgreSQL, the row has a value by the name of the table. For instance, SELECT foo FROM foo returns a row of type foo (which is a row type), from table foo. Here we test to see if that ROW is null. This will work so long as every column IS NOT NULL. And, if every column IS NULL in your table, then you're just trolling.

SELECT
  a.a1,
  b.b1,  
  CASE WHEN b IS NULL THEN 100 ELSE b.b2 END AS b2  
FROM a
LEFT OUTER JOIN b
  ON (a.a1 = b.b1);

Answer 4

As an alternative to coalescing column values one may want to use a default row on the non matching rows

Using tsql dialect based on the comment: I've added a tag to indicate I would accept a Postgres specific answer. Still, standard SQL would be preferred if possible.

Test data

SELECT *
INTO #a
FROM (VALUES (1)
           , (2)
           , (3)) AS x(a1);

SELECT *
INTO #b
FROM (VALUES (1, 10)
           , (3, NULL)
     ) AS x(b1, b2);

Basic query

SELECT *
FROM #a JOIN #b ON #a.a1 = #b.b1 -- all matches
UNION
SELECT #a.a1, NULL, 100 -- default row
FROM #a LEFT JOIN #b ON #a.a1 = #b.b1 WHERE b1 IS NULL -- where a1 <> b1;

Basic result

|a1|b1|b2 |
-----------
| 1| 1| 10|
| 2|  |100|
| 3| 3|   |

You could use this approach for a more set based approach, treating defaults as replacements for the difference in the two sets.

Here's a more advanced example where even/odd ids of a have different default b rows.

Advanced query

INSERT INTO #a
SELECT *
FROM (VALUES (5)) AS x(a1);

SELECT *
FROM #a
     JOIN #b ON #a.a1 = #b.b1 -- all matches
UNION
SELECT a1, NULL, val
FROM #a
     LEFT JOIN #b ON #a.a1 = #b.b1
     JOIN (
    SELECT *
    FROM (VALUES (0, 100) -- even default
               , (1, 99) -- odd default
         ) AS x(rem, val)
  ) t ON a1 % 2 = t.rem
WHERE b1 IS NULL -- where a1 <> b1;

Advanced result

|a1|b1|b2 |
-----------
| 1| 1| 10|
| 2|  |100|
| 3| 3|   |
| 5|  | 99|

Answer 5

OP has asked for an alternative to using the typical case / coalesce / ifnull method for replacing null values by specifying a different 'default value' for left joins instead of null. This is a valid request as it may be cumbersome if the field is used repeatedly or within complex calculations within the query or you want to differentiate between the 'row doesnt exist in joined table' signal and 'row exists but that field is null' result. The correct answer here is "no, that feature does not exist".