Re: FULL JOIN is only supported with merge-joinable join conditions

"Andrus" <kobruleht2(at)hot(dot)ee> writes:
>> I've yet to see a real-world case where a
>> non-merge-joinable full-join condition was really needed.

> I need to eliminate rows containing null value in left side table in full
> join.

> create table iandmed ( ametikoht integer );
> insert into iandmed values(1);
> insert into iandmed values(null);
> create table koosseis (ametikoht integer );
> insert into koosseis values(2);

> SELECT *
> FROM iandmed
> FULL JOIN koosseis ON iandmed.ametikoht=koosseis.ametikoht
> AND iandmed.ametikoht IS NOT NULL

> Required result:

> 1 null
> null 2

Well, if we did support that query as written, it would not produce the
result you want. With or without the IS NOT NULL part, the
null-containing row of iandmed will fail to join to every row of
koosseis, and will therefore produce a single output row with nulls for
the koosseis field(s). If you get a different result in some other
database, it's broken (nonstandard handling of NULL comparison maybe?).

I think the way to get the result you want is to suppress the
null-containing rows before they get to the FULL JOIN, like so:

regression=# SELECT *
FROM (SELECT * FROM iandmed WHERE ametikoht IS NOT NULL) AS iandmed
FULL JOIN koosseis ON iandmed.ametikoht=koosseis.ametikoht;
ametikoht | ametikoht
-----------+-----------
1 |
| 2
(2 rows)

regards, tom lane