pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOuter Joins
11+ messages / 9 participants
[nested] [flat]
* Outer Joins
@ 2001-02-04 02:37 Steve Doerr <sdoerr@swbell.net>
2001-02-05 07:44 ` Re: Outer Joins Anatoly K. Lasareff <tolik@aaanet.ru>
2001-02-05 23:54 ` Re: Outer Joins The Hermit Hacker <scrappy@hub.org>
0 siblings, 2 replies; 11+ messages in thread
From: Steve Doerr @ 2001-02-04 02:37 UTC (permalink / raw)
To: pgsql-admin
I saw that outer joins will be supported in ver. 7.1. Does anyone know
when this is expected, or if there is beta support for them currently?
Thanks,
Steve
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Outer Joins
2001-02-04 02:37 Outer Joins Steve Doerr <sdoerr@swbell.net>
@ 2001-02-05 07:44 ` Anatoly K. Lasareff <tolik@aaanet.ru>
1 sibling, 0 replies; 11+ messages in thread
From: tolik@aaanet.ru @ 2001-02-05 07:44 UTC (permalink / raw)
To: Steve Doerr <sdoerr@swbell.net>; +Cc: pgsql-admin
>>>>> "SD" == Steve Doerr <sdoerr@swbell.net> writes:
SD> I saw that outer joins will be supported in ver. 7.1. Does anyone know
SD> when this is expected, or if there is beta support for them currently?
Yes, they are supported in 7.1 beta. See doc.
--
Anatoly K. Lasareff Email: tolik@aaanet.ru
http://tolikus.hq.aaanet.ru:8080 Phone: (8632)-710071
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Outer Joins
2001-02-04 02:37 Outer Joins Steve Doerr <sdoerr@swbell.net>
@ 2001-02-05 23:54 ` The Hermit Hacker <scrappy@hub.org>
1 sibling, 0 replies; 11+ messages in thread
From: The Hermit Hacker @ 2001-02-05 23:54 UTC (permalink / raw)
To: Steve Doerr <sdoerr@swbell.net>; +Cc: pgsql-admin
full release is planned for end February, if all goes well ...
On Sat, 3 Feb 2001, Steve Doerr wrote:
> I saw that outer joins will be supported in ver. 7.1. Does anyone know
> when this is expected, or if there is beta support for them currently?
>
> Thanks,
> Steve
>
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: Outer Joins
@ 2001-02-05 15:30 Michael Davis <mdavis@sevainc.com>
0 siblings, 0 replies; 11+ messages in thread
From: Michael Davis @ 2001-02-05 15:30 UTC (permalink / raw)
To: 'Steve Doerr' <sdoerr@swbell.net>; pgsql-admin
The current source (7.1 beta 4, I think) has support for outer joins and they work great!
-----Original Message-----
From: Steve Doerr [SMTP:sdoerr@swbell.net]
Sent: Saturday, February 03, 2001 7:37 PM
To: pgsql-admin@postgresql.org
Subject: Outer Joins
I saw that outer joins will be supported in ver. 7.1. Does anyone know
when this is expected, or if there is beta support for them currently?
Thanks,
Steve
^ permalink raw reply [nested|flat] 11+ messages in thread
* outer joins
@ 2005-08-05 19:55 D Kavan <bitsandbytes88@hotmail.com>
2005-08-05 20:22 ` Re: outer joins Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 11+ messages in thread
From: D Kavan @ 2005-08-05 19:55 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: outer joins
2005-08-05 19:55 outer joins D Kavan <bitsandbytes88@hotmail.com>
@ 2005-08-05 20:22 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 11+ messages in thread
From: Michael Fuhr @ 2005-08-05 20:22 UTC (permalink / raw)
To: D Kavan <bitsandbytes88@hotmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: outer joins
@ 2005-08-05 20:14 Jason Minion <jason.minion@sigler.com>
2005-08-05 20:31 ` Re: outer joins D Kavan <bitsandbytes88@hotmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Jason Minion @ 2005-08-05 20:14 UTC (permalink / raw)
To: D Kavan <bitsandbytes88@hotmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: outer joins
2005-08-05 20:14 Re: outer joins Jason Minion <jason.minion@sigler.com>
@ 2005-08-05 20:31 ` D Kavan <bitsandbytes88@hotmail.com>
2005-08-05 21:31 ` Re: outer joins Jeff Boes <jeff@endpoint.com>
2005-08-05 22:00 ` Re: outer joins Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 11+ messages in thread
From: D Kavan @ 2005-08-05 20:31 UTC (permalink / raw)
To: pgsql-admin
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)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: outer joins
2005-08-05 20:14 Re: outer joins Jason Minion <jason.minion@sigler.com>
2005-08-05 20:31 ` Re: outer joins D Kavan <bitsandbytes88@hotmail.com>
@ 2005-08-05 21:31 ` Jeff Boes <jeff@endpoint.com>
1 sibling, 0 replies; 11+ messages in thread
From: Jeff Boes @ 2005-08-05 21:31 UTC (permalink / raw)
To: pgsql-admin
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;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: outer joins
2005-08-05 20:14 Re: outer joins Jason Minion <jason.minion@sigler.com>
2005-08-05 20:31 ` Re: outer joins D Kavan <bitsandbytes88@hotmail.com>
@ 2005-08-05 22:00 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 11+ messages in thread
From: Stephan Szabo @ 2005-08-05 22:00 UTC (permalink / raw)
To: D Kavan <bitsandbytes88@hotmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: outer joins
@ 2005-08-05 20:50 Jason Minion <jason.minion@sigler.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jason Minion @ 2005-08-05 20:50 UTC (permalink / raw)
To: D Kavan <bitsandbytes88@hotmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2005-08-05 22:00 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-04 02:37 Outer Joins Steve Doerr <sdoerr@swbell.net>
2001-02-05 07:44 ` Anatoly K. Lasareff <tolik@aaanet.ru>
2001-02-05 23:54 ` The Hermit Hacker <scrappy@hub.org>
2001-02-05 15:30 RE: Outer Joins Michael Davis <mdavis@sevainc.com>
2005-08-05 19:55 outer joins D Kavan <bitsandbytes88@hotmail.com>
2005-08-05 20:22 ` Re: outer joins Michael Fuhr <mike@fuhr.org>
2005-08-05 20:14 Re: outer joins Jason Minion <jason.minion@sigler.com>
2005-08-05 20:31 ` Re: outer joins D Kavan <bitsandbytes88@hotmail.com>
2005-08-05 21:31 ` Re: outer joins Jeff Boes <jeff@endpoint.com>
2005-08-05 22:00 ` Re: outer joins Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-08-05 20:50 Re: outer joins Jason Minion <jason.minion@sigler.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox