Обсуждение: outer joins
Hi, One the developers is saying that he can't do outer joins on postgresql. Is this true? We have postgresql 8.02. He is using this syntax: select from A left outer join b on A.id=B.id; This processes but comes back with a result like it was an inner join. ~DjK
If he's using a restriction upon an outer joined table in the where clause
it might be returning results like it was an inner join, ie:
select * from A left outer join B on A.id=B.id
where B.accessed>'2005-10-31';
Instead you'd want to check for the outer joined field having a null
value in the where clause, or move that restriction up to the on clause:
select * from A left outer join B
on A.id=B.id AND B.accessed>'2005-10-31';
or
select * from A left outer join B
on A.id=B.id
where (B.accessed IS NULL OR B.accessed>'2005-10-31');
I assure you, outer joins work just fine on PostgreSQL.
Jason
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of D Kavan
Sent: Friday, August 05, 2005 2:55 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] outer joins
Hi,
One the developers is saying that he can't do outer joins on postgresql.
Is this true? We have postgresql 8.02.
He is using this syntax:
select from A left outer join b on A.id=B.id;
This processes but comes back with a result like it was an inner join.
~DjK
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
On Fri, Aug 05, 2005 at 03:55:16PM -0400, D Kavan wrote: > One the developers is saying that he can't do outer joins on postgresql. > Is this true? We have postgresql 8.02. No, that isn't true. PostgreSQL has supported outer joins for a long time. > He is using this syntax: > select from A left outer join b on A.id=B.id; > > This processes but comes back with a result like it was an inner join. Please show a complete example with table definitions, the actual query (the above fails due to a syntax error), the results, and the results you were expecting. Here's an example of an outer join: CREATE TABLE a (id integer, adata text); INSERT INTO a VALUES (1, 'a one'); INSERT INTO a VALUES (2, 'a two'); CREATE TABLE b (id integer, bdata text); INSERT INTO b VALUES (1, 'b one'); INSERT INTO b VALUES (3, 'b three'); SELECT a.id AS aid, a.adata, b.id AS bid, b.bdata FROM a LEFT OUTER JOIN b ON a.id = b.id; aid | adata | bid | bdata -----+-------+-----+------- 1 | a one | 1 | b one 2 | a two | | (2 rows) An inner join would give this: SELECT a.id AS aid, a.adata, b.id AS bid, b.bdata FROM a INNER JOIN b ON a.id = b.id; aid | adata | bid | bdata -----+-------+-----+------- 1 | a one | 1 | b one (1 row) -- Michael Fuhr
Hi,
Thanks for the reply. That didn't help him, but he asked me to post this.
There are 66 project id's in the project table and 3 rows in the
project_members table for global_id 2915, but it only returns 3. I would
think it should return 66 rows, with 63 of them having a null pm.project_id.
SELECT
p.project_id, pm.project_id
FROM project p
LEFT OUTER JOIN project_members pm ON p.project_id = pm.project_id
where (pm.project_id is null or pm.global_id = 2915)
It looks like you're using two different fields in the where clause which
is kind of bad form. Try (pm.global_id is null or pm.global_id = 2915). The thing
is, if you have matching records in project_members for every record in project
with the IDs, then pm.project_id will never be null. Thus, the first part
of your OR in the where clause is always false, so the validity of the second part,
"pm.global_id = 2915", is the only thing that has any bearing on the records
returned. And if you only have 3 - you're only going to get three back.
Err... reading what I said above, it sounds kind of lame. Using two different
fields in the where clause specifically for this kind of construct would
be the bad form thing ;) Else, you are asking for unexpected results, such as
what you are getting now.
Jason
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of D Kavan
Sent: Friday, August 05, 2005 3:32 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] outer joins
Hi,
Thanks for the reply. That didn't help him, but he asked me to post this.
There are 66 project id's in the project table and 3 rows in the
project_members table for global_id 2915, but it only returns 3. I would
think it should return 66 rows, with 63 of them having a null pm.project_id.
SELECT
p.project_id, pm.project_id
FROM project p
LEFT OUTER JOIN project_members pm ON p.project_id = pm.project_id
where (pm.project_id is null or pm.global_id = 2915)
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
D Kavan wrote: > There are 66 project id's in the project table and 3 rows in the > project_members table for global_id 2915, but it only returns 3. I > would think it should return 66 rows, with 63 of them having a null > pm.project_id. > > SELECT > p.project_id, pm.project_id > FROM project p > LEFT OUTER JOIN project_members pm ON p.project_id = pm.project_id > where (pm.project_id is null or pm.global_id = 2915) > It would return 66, if all 66 p.project_id's were _missing_ from the set of pm.project_id's. A "LEFT JOIN" asks for all the rows in the left-hand table (p), joined to all the matching or missing rows in the right-hand table (pm). Then your "WHERE" clause filters all those but the missing ones OR the ones with global_id 2915. You need to check on the data with something like: select distinct project_id from project; select distinct project_id from project_members;
On Fri, 5 Aug 2005, D Kavan wrote: > > Hi, > > Thanks for the reply. That didn't help him, but he asked me to post this. > > There are 66 project id's in the project table and 3 rows in the > project_members table for global_id 2915, but it only returns 3. I would > think it should return 66 rows, with 63 of them having a null pm.project_id. > > SELECT > p.project_id, pm.project_id > FROM project p > LEFT OUTER JOIN project_members pm ON p.project_id = pm.project_id > where (pm.project_id is null or pm.global_id = 2915) That's not what that query does. That query matches up rows in project with rows in project_members based on project_id and then restricts to only those projects that didn't match or which matched to global_id=2915. I think what was desired can be gotten with something like: select p.project_id, pm.project_id from project p left outer join project_members pm on (p.project_id=pm.project_id and pm.global_id = 2915) Which I believe will extend a project with no matching global_id=2915 row with nulls.