19

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?

22
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
  • 2
    Please use ANSI SQL when the question is only tagged with sql (which means "SQL the query language". That tag does not denote any specific DBMS product or dialect). The part: [b2]=CASE WHEN ... END is an invalid (standard) SQL expression. – a_horse_with_no_name Jan 15 '14 at 18:42
  • I've added a tag to indicate I would accept a Postgres specific answer. Still, standard SQL would be preferred if possible. – Tom Ellis Jan 15 '14 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?" – Tom Ellis Jan 15 '14 at 19:47
  • 3
    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. – Mordechai Jan 15 '14 at 19:54
  • @MorDeror: OK, I suppose I was thinking there may be a syntax such as "LEFT OUTER JOIN ... ON ... DEFAULT b2 = ...". – Tom Ellis Jan 15 '14 at 19:59
1

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

Using a CASE statement

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);
  • Column b1 used in CASE solution does not need to be non-nullable. The construction works in either cases. – ypercubeᵀᴹ Mar 20 '17 at 23:19
1

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.