On Sun, 30 Mar 2008 [EMAIL PROTECTED] wrote: > 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?
As far as I can tell, all the results you got were exactly what the SQL spec requires for the queries and data you gave, so this really doesn't belong on pgsql-bugs. I'm not redirecting it now, but if you want to follow-up please do so on pgsql-general or pgsql-sql. > 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) First the full outer join is done which may NULL extend an a row to the right or a b row to the left. Then the where clause is run. After the full outer join, an a row that was null extended to the right will have b.groups IS NULL, but so will an a row that matched b row with a NULL for b.groups. The same is basically true in the other direction as well. The where clause's select condition returns true in both cases, which is why the added 999 row shows up. Similarly, the variant you used later: SELECT * FROM a FULL OUTER JOIN b ON a.num1 = b.num1 where (a.groups =1) and (b.groups=2) removes the null extended rows for the same reason. The full outer join produces them, but in this case they do not pass the where clause's search condition. For queries of this type, usually the subselect-in-from form has the intended behavior. You filter the left and right side to have the subset you care about and then outer join those subsets. Something like: select * from (select * from a where groups = 1) a full outer join (select * from b where groups = 2) b ON a.num1=b.num1; -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
