pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Outer 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