2 added 336 characters in body

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

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