Thread: [SQL] OUTER JOINS

[SQL] OUTER JOINS

From
Dan Janowski
Date:
I've looked through the docs and mail lists but I am coming
up short on references for LEFT/RIGHT/FULL OUTER JOIN
avalability
or functional workarounds. It seems that PostgreSQL does
not have it.

Any recommendations on how to aproximate the functionality?

As a curiosity, is there a problem with supporting these
OUTER joins or is it merely on the list?

Many thanks,

Dan

--
Dan Janowski     danj@3skel.com       Triskelion Systems,
Inc.     Bronx, NY



Update on 6.5

From
Chairudin Sentosa
Date:
Hi,

I am using postgresql 6.5 snapshot.
I can not update.
Could anyone tell me what's wrong, please?

update ibs_br_all_total_units tr set first_name=
(select first_name from ibs_subscriber tnwhere tr.pin = tn.pin
);

"/tmp/psql.60000.13342" 4 lines, 117 characters
ERROR:  parser: syntax error at or near "tr"

Thanks.

Regards,
Chai




Re: [SQL] Update on 6.5

From
José Soares
Date:
Chairudin Sentosa ha scritto:

> Hi,
>
> I am using postgresql 6.5 snapshot.
> I can not update.
> Could anyone tell me what's wrong, please?
>
> update ibs_br_all_total_units tr set first_name=
> (select first_name from ibs_subscriber tn
>  where tr.pin = tn.pin
> );
>
> "/tmp/psql.60000.13342" 4 lines, 117 characters
> ERROR:  parser: syntax error at or near "tr"
>

AFAIK PostgreSQL doesn't allow subselects on UPDATE.

José



Re: [SQL] OUTER JOINS

From
Joe Shevland
Date:
I didn't see a response for this, so here goes... I believe you can
implement joins using the followng syntax:

select t1.value from table_one t1, table_two t2 where t1.key = t2.key;

So this would be a straight LEFT join in my understanding. Now, I'm
venturing beyond my experience with PostgreSQL here, but you may be able
to use the associated SQL syntaxes:

select ... where t1.key *= t2.key
select ... where t1.key =* t2.key

etc...

I hope I'm right here :)

Regards,
Joe.

Dan Janowski wrote:
> 
> I've looked through the docs and mail lists but I am coming
> up short on references for LEFT/RIGHT/FULL OUTER JOIN
> avalability
> or functional workarounds. It seems that PostgreSQL does
> not have it.
> 
> Any recommendations on how to aproximate the functionality?
> 
> As a curiosity, is there a problem with supporting these
> OUTER joins or is it merely on the list?
> 
> Many thanks,
> 
> Dan
> 
> --
> Dan Janowski     danj@3skel.com       Triskelion Systems,
> Inc.     Bronx, NY

-- 
---------------------------------------------,-._|\  | Joe Shevland
/      \ | Principal Consultant
\_,--._/ | Turnaround Solutions Pty. Ltd.     v  | http://www.TurnAround.com.au
---------------------------------------------
Skate to where the puck is going and not to
where it has been - Wayne Gretzky


Re: [SQL] OUTER JOINS

From
Dan Janowski
Date:
Thanks for giving it a stab. Looking at the available
operators,
there is no =* or *= ops available. Although I am curious
where it
comes from.

Dan


Joe Shevland wrote:
> ... 
> select t1.value from table_one t1, table_two t2 where t1.key = t2.key;
> 
> So this would be a straight LEFT join in my understanding. Now, I'm
> venturing beyond my experience with PostgreSQL here, but you may be able
> to use the associated SQL syntaxes:
> 
> select ... where t1.key *= t2.key
> select ... where t1.key =* t2.key
> ...
>
> Dan Janowski wrote:
> >
> > I've looked through the docs and mail lists but I am coming
> > up short on references for LEFT/RIGHT/FULL OUTER JOIN
> > avalability
> > or functional workarounds. It seems that PostgreSQL does
> > not have it.
> >
> > Any recommendations on how to aproximate the functionality?
> >
> > As a curiosity, is there a problem with supporting these
> > OUTER joins or is it merely on the list?
> >

--
Dan Janowski     danj@3skel.com       Triskelion Systems,
Inc.     Bronx, NY


RE: [SQL] OUTER JOINS

From
Michael J Davis
Date:
As far as I have been able to determine, outer joins are not supported in
PostgreSQL.

> -----Original Message-----
> From:    Joe Shevland [SMTP:J_Shevland@TurnAround.com.au]
> Sent:    Monday, April 19, 1999 12:27 AM
> To:    Dan Janowski
> Cc:    pgsql-sql@postgreSQL.org
> Subject:    Re: [SQL] OUTER JOINS
> 
> I didn't see a response for this, so here goes... I believe you can
> implement joins using the followng syntax:
> 
> select t1.value from table_one t1, table_two t2 where t1.key = t2.key;
> 
> So this would be a straight LEFT join in my understanding. Now, I'm
> venturing beyond my experience with PostgreSQL here, but you may be able
> to use the associated SQL syntaxes:
> 
> select ... where t1.key *= t2.key
> select ... where t1.key =* t2.key
> 
> etc...
> 
> I hope I'm right here :)
> 
> Regards,
> Joe.
> 
> Dan Janowski wrote:
> > 
> > I've looked through the docs and mail lists but I am coming
> > up short on references for LEFT/RIGHT/FULL OUTER JOIN
> > avalability
> > or functional workarounds. It seems that PostgreSQL does
> > not have it.
> > 
> > Any recommendations on how to aproximate the functionality?
> > 
> > As a curiosity, is there a problem with supporting these
> > OUTER joins or is it merely on the list?
> > 
> > Many thanks,
> > 
> > Dan
> > 
> > --
> > Dan Janowski     danj@3skel.com       Triskelion Systems,
> > Inc.     Bronx, NY
> 
> -- 
> ---------------------------------------------
>  ,-._|\  | Joe Shevland
> /      \ | Principal Consultant
> \_,--._/ | Turnaround Solutions Pty. Ltd.
>       v  | http://www.TurnAround.com.au
> ---------------------------------------------
> Skate to where the puck is going and not to
> where it has been - Wayne Gretzky


RE: [SQL] OUTER JOINS

From
Michael J Davis
Date:
*= and =* are Oracle syntax for outer joins.

> -----Original Message-----
> From:    Dan Janowski [SMTP:danj@3skel.com]
> Sent:    Monday, April 19, 1999 7:27 AM
> To:    J_Shevland@TurnAround.com.au
> Cc:    pgsql-sql@postgreSQL.org
> Subject:    Re: [SQL] OUTER JOINS
> 
> Thanks for giving it a stab. Looking at the available
> operators,
> there is no =* or *= ops available. Although I am curious
> where it
> comes from.
> 
> Dan
> 
> 
> Joe Shevland wrote:
> > ... 
> > select t1.value from table_one t1, table_two t2 where t1.key = t2.key;
> > 
> > So this would be a straight LEFT join in my understanding. Now, I'm
> > venturing beyond my experience with PostgreSQL here, but you may be able
> > to use the associated SQL syntaxes:
> > 
> > select ... where t1.key *= t2.key
> > select ... where t1.key =* t2.key
> > ...
> >
> > Dan Janowski wrote:
> > >
> > > I've looked through the docs and mail lists but I am coming
> > > up short on references for LEFT/RIGHT/FULL OUTER JOIN
> > > avalability
> > > or functional workarounds. It seems that PostgreSQL does
> > > not have it.
> > >
> > > Any recommendations on how to aproximate the functionality?
> > >
> > > As a curiosity, is there a problem with supporting these
> > > OUTER joins or is it merely on the list?
> > >
> 
> --
> Dan Janowski     danj@3skel.com       Triskelion Systems,
> Inc.     Bronx, NY


RE:[SQL] OUTER JOINS

From
Michael J Davis
Date:
As far as I have been able to determine, outer joins are not supported in
PostgreSQL.
-----Original Message-----From:    Joe Shevland [SMTP:J_Shevland@TurnAround.com.au]
<mailto:[SMTP:J_Shevland@TurnAround.com.au]> Sent:    Monday, April 19, 1999 12:27 AMTo:    Dan JanowskiCc:
pgsql-sql@postgreSQL.org<mailto:pgsql-sql@postgreSQL.org> Subject:    Re: [SQL] OUTER JOINS
 
I didn't see a response for this, so here goes... I believe you canimplement joins using the followng syntax:
select t1.value from table_one t1, table_two t2 where t1.key =
t2.key;
So this would be a straight LEFT join in my understanding. Now, I'mventuring beyond my experience with PostgreSQL here,
butyou may be
 
ableto use the associated SQL syntaxes:
select ... where t1.key *= t2.keyselect ... where t1.key =* t2.key
etc...
I hope I'm right here :)
Regards,Joe.
Dan Janowski wrote:> > I've looked through the docs and mail lists but I am coming> up short on references for
LEFT/RIGHT/FULLOUTER JOIN> avalability> or functional workarounds. It seems that PostgreSQL does> not have it.> > Any
recommendationson how to aproximate the functionality?> > As a curiosity, is there a problem with supporting these>
OUTERjoins or is it merely on the list?> > Many thanks,> > Dan> > --> Dan Janowski     danj@3skel.com
<mailto:danj@3skel.com>
Triskelion Systems,> Inc.     Bronx, NY
-- --------------------------------------------- ,-._|\  | Joe Shevland/      \ | Principal Consultant\_,--._/ |
TurnaroundSolutions Pty. Ltd.      v  | http://www.TurnAround.com.au
 
<http://www.TurnAround.com.au> ---------------------------------------------Skate to where the puck is going and not
towhereit has been - Wayne Gretzky
 


RE: [SQL] OUTER JOINS

From
Michael J Davis
Date:
As far as I have been able to determine, outer joins are not
supported in PostgreSQL.
    -----Original Message-----    From:    Joe Shevland [SMTP:J_Shevland@TurnAround.com.au]    Sent:    Monday, April
19,1999 12:27 AM    To:    Dan Janowski    Cc:    pgsql-sql@postgreSQL.org    Subject:    Re: [SQL] OUTER JOINS
 
    I didn't see a response for this, so here goes... I believe
you can    implement joins using the followng syntax:
    select t1.value from table_one t1, table_two t2 where t1.key
= t2.key;
    So this would be a straight LEFT join in my understanding.
Now, I'm    venturing beyond my experience with PostgreSQL here, but you
may be able    to use the associated SQL syntaxes:
    select ... where t1.key *= t2.key    select ... where t1.key =* t2.key
    etc...
    I hope I'm right here :)
    Regards,    Joe.
    Dan Janowski wrote:    >     > I've looked through the docs and mail lists but I am
coming    > up short on references for LEFT/RIGHT/FULL OUTER JOIN    > avalability    > or functional workarounds. It
seemsthat PostgreSQL does    > not have it.    >     > Any recommendations on how to aproximate the
 
functionality?    >     > As a curiosity, is there a problem with supporting these    > OUTER joins or is it merely on
thelist?    >     > Many thanks,    >     > Dan    >     > --    > Dan Janowski     danj@3skel.com       Triskelion
Systems,   > Inc.     Bronx, NY
 
    --     ---------------------------------------------     ,-._|\  | Joe Shevland    /      \ | Principal Consultant
 \_,--._/ | Turnaround Solutions Pty. Ltd.          v  | http://www.TurnAround.com.au
---------------------------------------------   Skate to where the puck is going and not to    where it has been -
WayneGretzky
 


RE: [SQL] OUTER JOINS

From
Michael J Davis
Date:
*= and =* are Oracle syntax for outer joins.
    -----Original Message-----    From:    Dan Janowski [SMTP:danj@3skel.com]    Sent:    Monday, April 19, 1999 7:27
AM   To:    J_Shevland@TurnAround.com.au    Cc:    pgsql-sql@postgreSQL.org    Subject:    Re: [SQL] OUTER JOINS
 
    Thanks for giving it a stab. Looking at the available    operators,    there is no =* or *= ops available. Although
Iam curious    where it    comes from.
 
    Dan

    Joe Shevland wrote:    > ...     > select t1.value from table_one t1, table_two t2 where
t1.key = t2.key;    >     > So this would be a straight LEFT join in my understanding.
Now, I'm    > venturing beyond my experience with PostgreSQL here, but
you may be able    > to use the associated SQL syntaxes:    >     > select ... where t1.key *= t2.key    > select ...
wheret1.key =* t2.key    > ...    >    > Dan Janowski wrote:    > >    > > I've looked through the docs and mail lists
butI am
 
coming    > > up short on references for LEFT/RIGHT/FULL OUTER JOIN    > > avalability    > > or functional
workarounds.It seems that PostgreSQL does    > > not have it.    > >    > > Any recommendations on how to aproximate
the
functionality?    > >    > > As a curiosity, is there a problem with supporting these    > > OUTER joins or is it
merelyon the list?    > >
 
    --    Dan Janowski     danj@3skel.com       Triskelion Systems,    Inc.     Bronx, NY


Re: [SQL] OUTER JOINS

From
Joe Shevland
Date:
Aha, I was wondering where they were dredged from :) Is 6.5 going to
support OJ's?

Michael J Davis wrote:
> 
>         *= and =* are Oracle syntax for outer joins.
> 
>                 -----Original Message-----
>                 From:   Dan Janowski [SMTP:danj@3skel.com]
>                 Sent:   Monday, April 19, 1999 7:27 AM
>                 To:     J_Shevland@TurnAround.com.au
>                 Cc:     pgsql-sql@postgreSQL.org
>                 Subject:        Re: [SQL] OUTER JOINS
> 
>                 Thanks for giving it a stab. Looking at the available
>                 operators,
>                 there is no =* or *= ops available. Although I am curious
>                 where it
>                 comes from.
> 
>                 Dan
> 
>                 Joe Shevland wrote:
>                 > ...
>                 > select t1.value from table_one t1, table_two t2 where
> t1.key = t2.key;
>                 >
>                 > So this would be a straight LEFT join in my understanding.
> Now, I'm
>                 > venturing beyond my experience with PostgreSQL here, but
> you may be able
>                 > to use the associated SQL syntaxes:
>                 >
>                 > select ... where t1.key *= t2.key
>                 > select ... where t1.key =* t2.key
>                 > ...
>                 >
>                 > Dan Janowski wrote:
>                 > >
>                 > > I've looked through the docs and mail lists but I am
> coming
>                 > > up short on references for LEFT/RIGHT/FULL OUTER JOIN
>                 > > avalability
>                 > > or functional workarounds. It seems that PostgreSQL does
>                 > > not have it.
>                 > >
>                 > > Any recommendations on how to aproximate the
> functionality?
>                 > >
>                 > > As a curiosity, is there a problem with supporting these
>                 > > OUTER joins or is it merely on the list?
>                 > >
> 
>                 --
>                 Dan Janowski     danj@3skel.com       Triskelion Systems,
>                 Inc.     Bronx, NY

-- 
---------------------------------------------,-._|\  | Joe Shevland
/      \ | Principal Consultant
\_,--._/ | Turnaround Solutions Pty. Ltd.     v  | http://www.TurnAround.com.au
---------------------------------------------
Skate to where the puck is going and not to
where it has been - Wayne Gretzky


RE: [SQL] OUTER JOINS

From
Michael J Davis
Date:
I don't think outer joins are planned for 6.5.  Possibly 6.6?
-----Original Message-----From:    Joe Shevland [SMTP:J_Shevland@TurnAround.com.au]Sent:    Monday, April 19, 1999 9:33
PMTo:   Michael J DavisCc:    'pgsql-sql@postgreSQL.org'Subject:    Re: [SQL] OUTER JOINS
 
Aha, I was wondering where they were dredged from :) Is 6.5 going tosupport OJ's?
Michael J Davis wrote:> >         *= and =* are Oracle syntax for outer joins.> >                 -----Original
Message----->                From:   Dan Janowski [SMTP:danj@3skel.com]>                 Sent:   Monday, April 19, 1999
7:27AM>                 To:     J_Shevland@TurnAround.com.au>                 Cc:     pgsql-sql@postgreSQL.org>
       Subject:        Re: [SQL] OUTER JOINS> >                 Thanks for giving it a stab. Looking at the
 
available>                 operators,>                 there is no =* or *= ops available. Although I am
curious>                 where it>                 comes from.> >                 Dan> >                 Joe Shevland
wrote:>                > ...>                 > select t1.value from table_one t1, table_two t2
 
where> t1.key = t2.key;>                 >>                 > So this would be a straight LEFT join in my
understanding.> Now, I'm>                 > venturing beyond my experience with PostgreSQL
here, but> you may be able>                 > to use the associated SQL syntaxes:>                 >>                 >
select... where t1.key *= t2.key>                 > select ... where t1.key =* t2.key>                 > ...>
     >>                 > Dan Janowski wrote:>                 > >>                 > > I've looked through the docs
andmail lists
 
but I am> coming>                 > > up short on references for LEFT/RIGHT/FULL
OUTER JOIN>                 > > avalability>                 > > or functional workarounds. It seems that
PostgreSQL does>                 > > not have it.>                 > >>                 > > Any recommendations on how
toaproximate the> functionality?>                 > >>                 > > As a curiosity, is there a problem with
 
supporting these>                 > > OUTER joins or is it merely on the list?>                 > >> >
-->                Dan Janowski     danj@3skel.com       Triskelion
 
Systems,>                 Inc.     Bronx, NY
-- --------------------------------------------- ,-._|\  | Joe Shevland/      \ | Principal Consultant\_,--._/ |
TurnaroundSolutions Pty. Ltd.      v  | http://www.TurnAround.com.au---------------------------------------------Skate
towhere the puck is going and not towhere it has been - Wayne Gretzky
 


Re: [SQL] Update on 6.5

From
Chairudin Sentosa
Date:
José Soares wrote:

> Chairudin Sentosa ha scritto:
>
> > Hi,
> >
> > I am using postgresql 6.5 snapshot.
> > I can not update.
> > Could anyone tell me what's wrong, please?
> >
> > update ibs_br_all_total_units tr set first_name=
> > (select first_name from ibs_subscriber tn
> >  where tr.pin = tn.pin
> > );
> >
> > "/tmp/psql.60000.13342" 4 lines, 117 characters
> > ERROR:  parser: syntax error at or near "tr"
> >
>
> AFAIK PostgreSQL doesn't allow subselects on UPDATE.
>
> José

Hi Jose,

How should I do it in PostgreSQL?

Thanks

Regards,
Chai



Re: [SQL] Update on 6.5

From
José Soares
Date:
Try this function:

create function a(int,text) returns text as
'begin       update ibs_br_all_total_units set first_name = $2 where pin =
$1;       RETURN $2;end; ' language 'plpgsql';

create table ibs_subscriber( pin int, first_name text);
insert into ibs_subscriber values(1,'pippo');
insert into ibs_subscriber values(3,'pluto');

create table ibs_br_all_total_units( pin int, first_name text);
insert into ibs_br_all_total_units values(1,'text');
insert into ibs_br_all_total_units values(2,'text');
insert into ibs_br_all_total_units values(3,'text');

select * from  ibs_subscriber ;
pin|first_name
---+---------- 1|pippo 3|pluto
(2 rows)

select * from  ibs_br_all_total_units ;
pin|first_name
---+---------- 1|text 2|text 3|text
(3 rows)

select a(pin,first_name) from ibs_subscriber ;
a
-----
pippo
pluto
(2 rows)

select * from  ibs_br_all_total_units ;
pin|first_name
---+---------- 2|text 1|pippo 3|pluto
(3 rows)

José


Chairudin Sentosa ha scritto:

> José Soares wrote:
>
> > Chairudin Sentosa ha scritto:
> >
> > > Hi,
> > >
> > > I am using postgresql 6.5 snapshot.
> > > I can not update.
> > > Could anyone tell me what's wrong, please?
> > >
> > > update ibs_br_all_total_units tr set first_name=
> > > (select first_name from ibs_subscriber tn
> > >  where tr.pin = tn.pin
> > > );
> > >
> > > "/tmp/psql.60000.13342" 4 lines, 117 characters
> > > ERROR:  parser: syntax error at or near "tr"
> > >
> >
> > AFAIK PostgreSQL doesn't allow subselects on UPDATE.
> >
> > José
>
> Hi Jose,
>
> How should I do it in PostgreSQL?
>
> Thanks
>
> Regards,
> Chai



Re: [SQL] OUTER JOINS

From
Herouth Maoz
Date:
At 16:26 +0300 on 19/04/1999, Dan Janowski wrote:


> Thanks for giving it a stab. Looking at the available
> operators,
> there is no =* or *= ops available. Although I am curious
> where it
> comes from.

To the best of my knowledge, outer joins are not yet supported in
PostgreSQL. As far as I recall, you can achieve the same effect with a
union.

For example, if you have a people table and a pets table, where each
person's pet is joined to the owner by the owner's id colum, you'll
theoretically do something like:
 SELECT surname, firstname, pet_name FROM people, pets WHERE id *= owner_id ORDER BY surname, firstname;

So instead, you'll have to do something like:
 SELECT surname, firstname, pet_name FROM people, pets WHERE id = owner_id UNION SELECT surname, firstname, null FROM
peopleWHERE not exist (    SELECT 1    FROM pets    WHERE owner_id = id ) ORDER BY surname, firstname;
 

Excuse me if I goofed something syntactically, I don't have time to
actually create the tables and test the above. Note, however, that a UNION
removes duplicates.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma