The problem with FULL JOIN

[<Eugen(dot)Konkov(at)aldec(dot)com>]

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: 123
Description: Binary data