outer joins

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

outer joins

dx k9
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
Reply | Threaded
Open this post in threaded view
|

Re: outer joins

Jason Minion
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: [hidden email]
[mailto:[hidden email]]On Behalf Of D Kavan
Sent: Friday, August 05, 2005 2:55 PM
To: [hidden email]
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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: outer joins

Michael Fuhr
In reply to this post by dx k9
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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: outer joins

dx k9
In reply to this post by Jason Minion

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
Reply | Threaded
Open this post in threaded view
|

Re: outer joins

Jason Minion
In reply to this post by dx k9
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: [hidden email]
[mailto:[hidden email]]On Behalf Of D Kavan
Sent: Friday, August 05, 2005 3:32 PM
To: [hidden email]
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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: outer joins

Jeff Boes
In reply to this post by dx k9
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;


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: outer joins

Stephan Szabo
In reply to this post by dx k9
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.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match