45

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?

Improve this question

5 Answers 5

Reset to default
49 
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
Improve this answer
9
  • I've added a tag to indicate I would accept a Postgres specific answer. Still, standard SQL would be preferred if possible. Commented Jan 15, 2014 at 19:46
  • @Kin: as stated in my question I know that "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?" Commented Jan 15, 2014 at 19:47
  • 4
    since you want to distinguish between NULLs that occur because of a JOIN and those that are "naturally" present, it is inevitable that you'd have to examine b1. If that's what you meant by "I could do it "by hand"", than yes, that is the only way. Commented Jan 15, 2014 at 19:54
  • @MorDeror: OK, I suppose I was thinking there may be a syntax such as "LEFT OUTER JOIN ... ON ... DEFAULT b2 = ...". Commented Jan 15, 2014 at 19:59
  • 3
    WIthout explanation, I'm tempted to downvote this. This answer does not live up to the quality of DBA.SE. Commented Mar 20, 2017 at 22:41
23

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;
Improve this answer
6
  • 5
    This is the best answer so far. Commented Dec 24, 2019 at 11:57
  • @ypercubeᵀᴹ Really? What part? The point was to illustrate the COALESCE function in contrast to the CASE WHEN syntax, based on the first query presented. and the accepted answer.. Commented Mar 23, 2021 at 12:19
  • Yeah.. re-reading the question i see your point.. but like I said above I was trying to illustrate the COALESCE function more than as a full answer. I will correct. Thank you for pointing that out. Commented Mar 23, 2021 at 13:53
  • yep that works. Commented Mar 23, 2021 at 14:21
  • 1
    Yeah I agree. Since you incorporated them into the answer though, it was just noise. It's really hard getting info from the comments of a question or answer that has 20+ of them. Commented Mar 24, 2021 at 13:59
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);
Improve this answer
1
  • 1
    Column b1 used in CASE solution does not need to be non-nullable. The construction works in either cases. Commented Mar 20, 2017 at 23:19
2

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|
Improve this answer
7
  • 1
    Nice but # is used only in SQL Server for temp tables. Not in Postgres. Commented Mar 23, 2021 at 9:27
  • @ypercubeᵀᴹ question isn't postgres specific but I added a note that the answer uses TSQL Commented Mar 23, 2021 at 11:25
  • @JohnK.N. the author tagged the question as postgresql because the first answer was in postgres dialect, there's nothing specificly postgres about the question. I would change the syntax of the temp tables but I can't be bothered since it has nothing to do with the answer itself Commented Mar 23, 2021 at 12:30
  • Seen in a comment on an answer: "I've added a tag to indicate I would accept a Postgres specific answer. Still, standard SQL would be preferred if possible" so I guess the community could go with your answer. :-) Commented Mar 23, 2021 at 12:34
  • 1
    @JohnK.N. the accepted answer is standard SQL. This one as well (but I'm not sure if the fancy # are allowed in table names ;) Commented Mar 23, 2021 at 13:41
0

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".

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.