The problem with FULL JOIN
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?
Attachments:
<Eugen.Konkov@aldec.com> writes:
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?
...
SELECT *
FROM (select * from a where a.groups = 1) AS a
FULL OUTER JOIN (select * from b where b.groups = 2) AS b
ON (a.num1=b.num1)
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!
On Sun, 30 Mar 2008 Eugen.Konkov@aldec.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;