"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
