Обсуждение: outer joins

Hi all,

I'm new to postgre, I've changed my work and consequently now i'm moving
from MS plaform.
In MS SQL there are such constructs left or right outer join, in postgres
there are no such thing

Can You offer me strategy to make query that selects from table (a) and
joins to it another (b)
on e.g. a.id=b.aid but joins so that in case there is no value in table b
NULL is left in result:
in MS SQL i've used query:

select a.id, b.name from a left outer join b on a.id=b.aid

table a          table b

id |             aid | name
-----------      ----------------
1                1   | Tom
2                3   | Sam

result:
a.id  |  b.name
---------------------
1     | Tom
2     | NULL

thank you in advance

Algirdas Šakmanas
IT manager
+370 99 90369
asakmanas@grafton.lt
Grafton Entertainment
http://www.tvnet.lt

Hello,

I believe these are supported in 7.1


On Wed, 4 Apr 2001, [iso-8859-4] Algirdas ©akmanas wrote:

>Hi all,
>
>I'm new to postgre, I've changed my work and consequently now i'm moving
>from MS plaform.
>In MS SQL there are such constructs left or right outer join, in postgres
>there are no such thing
>
>Can You offer me strategy to make query that selects from table (a) and
>joins to it another (b)
>on e.g. a.id=b.aid but joins so that in case there is no value in table b
>NULL is left in result:
>in MS SQL i've used query:
>
>select a.id, b.name from a left outer join b on a.id=b.aid
>
>table a          table b
>
>id |             aid | name
>-----------      ----------------
>1                1   | Tom
>2                3   | Sam
>
>result:
>a.id  |  b.name
>---------------------
>1     | Tom
>2     | NULL
>
>thank you in advance
>
>Algirdas ©akmanas
>IT manager
>+370 99 90369
>asakmanas@grafton.lt
>Grafton Entertainment
>http://www.tvnet.lt
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

-- 
--
<COMPANY>CommandPrompt    - http://www.commandprompt.com    </COMPANY>
<PROJECT>OpenDocs, LLC.    - http://www.opendocs.org    </PROJECT>
<PROJECT>LinuxPorts     - http://www.linuxports.com     </PROJECT>
<WEBMASTER>LDP        - http://www.linuxdoc.org    </WEBMASTER>
--
Instead of asking why a piece of software is using "1970s technology,"
start asking why software is ignoring 30 years of accumulated wisdom.
--

I've read that the version 7.1 provides outer join feature. Since I did not
try it yet, I've no more information about it.


> -----Message d'origine-----
> De:    Algirdas Sakmanas [SMTP:asakmanas@grafton.lt]
> Date:    mercredi 4 avril 2001 13:03
> À:    pgsql-sql@postgresql.org
> Objet:    [SQL] outer joins
> 
> Hi all,
> 
> I'm new to postgre, I've changed my work and consequently now i'm moving
> from MS plaform.
> In MS SQL there are such constructs left or right outer join, in postgres
> there are no such thing
> 
> Can You offer me strategy to make query that selects from table (a) and
> joins to it another (b)
> on e.g. a.id=b.aid but joins so that in case there is no value in table b
> NULL is left in result:
> in MS SQL i've used query:
> 
> select a.id, b.name from a left outer join b on a.id=b.aid
> 
> table a          table b
> 
> id |             aid | name
> -----------      ----------------
> 1                1   | Tom
> 2                3   | Sam
> 
> result:
> a.id  |  b.name
> ---------------------
> 1     | Tom
> 2     | NULL
> 
> thank you in advance
> 
> Algirdas Sakmanas
> IT manager
> +370 99 90369
> asakmanas@grafton.lt
> Grafton Entertainment
> http://www.tvnet.lt
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Algirdas,

This should do the trick:

SELECT a.id,b.name FROM a,b WHERE a.id=b.id UNION SELECT id,null FROM a WHERE id NOT IN (SELECT id FROM b);


Troy

> 
> Hi all,
> 
> I'm new to postgre, I've changed my work and consequently now i'm moving
> from MS plaform.
> In MS SQL there are such constructs left or right outer join, in postgres
> there are no such thing
> 
> Can You offer me strategy to make query that selects from table (a) and
> joins to it another (b)
> on e.g. a.id=b.aid but joins so that in case there is no value in table b
> NULL is left in result:
> in MS SQL i've used query:
> 
> select a.id, b.name from a left outer join b on a.id=b.aid
> 
> table a          table b
> 
> id |             aid | name
> -----------      ----------------
> 1                1   | Tom
> 2                3   | Sam
> 
> result:
> a.id  |  b.name
> ---------------------
> 1     | Tom
> 2     | NULL
> 
> thank you in advance
> 
> Algirdas ©akmanas
> IT manager
> +370 99 90369
> asakmanas@grafton.lt
> Grafton Entertainment
> http://www.tvnet.lt
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>