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?
123
Description: Binary data
-- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
