The problem with FULL JOIN

From: <Eugen(dot)Konkov(at)aldec(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: The problem with FULL JOIN
Date: 2008-03-30 17:13:23
Message-ID: 048f01c89289$5d464400$1200a8c0@kharkov.localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

PROBLEM:
How to FULL JOIN groups=1 from table 'a' with groups=2 from table 'b'
and exclude original NULL groups not thouse which FULL JOIN produce?

DESCRIPTION:
I have a schema which is attached at file '123':

while FULL JOIN ing I get:
postgres=# SELECT * FROM a FULL JOIN b ON a.num1 = b.num1;
num1 | num2 | groups | num1 | num2 | groups
------+------+--------+------+------+--------
1 | 1 | 1 | 1 | 1 | 1
1 | 1 | 1 | 1 | 1 | 2
1 | 1 | 2 | 1 | 1 | 1
1 | 1 | 2 | 1 | 1 | 2
2 | 2 | 1 | | |
2 | 2 | 2 | | |
| | | 3 | 3 | 1
| | | 3 | 3 | 2
(8 rows)

All is ok here,
BUT when I want to full join groups 1 from table a with groups 2 from table 2 I have get a PROBLEM
SELECT *
FROM a
FULL OUTER JOIN b ON a.num1 = b.num1
where (a.groups =1 or a.groups is NULL) and (b.groups=2 or b.groups is NULL)

num1 | num2 | groups | num1 | num2 | groups
------+------+--------+------+------+--------
1 | 1 | 1 | 1 | 1 | 2
2 | 2 | 1 | | |
| | | 3 | 3 | 2
(3 rows)

If table column 'groups' of table a and/or b has no NULL. I get what I want,
BUT when they have, expected result are differ from actual
INSERT INTO a values( 999,999, null);
INSERT INTO b value (999,999,null);

SELECT *
FROM a
FULL OUTER JOIN b ON a.num1 = b.num1
where (a.groups =1 or a.groups is NULL) and (b.groups=2 or b.groups is NULL)
num1 | num2 | groups | num1 | num2 | groups
------+------+--------+------+------+--------
1 | 1 | 1 | 1 | 1 | 2
2 | 2 | 1 | | |
| | | 3 | 3 | 2
999 | 999 | | 999 | 999 |
(4 rows)

Here I do not expect last row.
It seem that it is IMPOSSIBLE to filter out rows with groups which have NULL values
When I write:
SELECT * FROM a FULL OUTER JOIN b ON a.num1 = b.num1
where (a.groups =1) and (b.groups=2)
num1 | num2 | groups | num1 | num2 | groups
------+------+--------+------+------+--------
1 | 1 | 1 | 1 | 1 | 2
(1 row)

I lose rows which FULL JOIN must produce ((
http://www.postgresql.org/docs/8.3/static/queries-table-expressions.html
>FULL OUTER JOIN
First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Also, for each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added.

How to FULL JOIN groups=1 from table 'a' with groups=2 from table 'b'
and exclude original NULL groups not thouse which FULL JOIN produce?

Attachment Content-Type Size
123 application/octet-stream 1.2 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Gregory Stark 2008-03-30 17:40:53 Re: BUG #4069: Wrong tip
Previous Message Dave Page 2008-03-30 15:02:56 Re: BUG #4069: Wrong tip