Re: The problem with FULL JOIN

On Sun, 30 Mar 2008 Eugen(dot)Konkov(at)aldec(dot)com 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;