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

"Andrus" <kobruleht2@hot.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