2 added 336 characters in body
source | link

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 b.b2100 ELSE 100b.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);

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 THEN b.b2 ELSE 100 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.

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

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);
1
source | link

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 THEN b.b2 ELSE 100 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.

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