Re: syntax of outer join in 7.1devel

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Poul L(dot) Christiansen" <poulc(at)cs(dot)auc(dot)dk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: syntax of outer join in 7.1devel
Date: 2000-12-07 01:55:39
Message-ID: 20409.976154139@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Poul L. Christiansen" <poulc(at)cs(dot)auc(dot)dk> writes:
> <html><head></head><body>Using the example:<br>
> SELECT '' AS "xxx", *<br>
> FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i);<br>
> <br>
> ..doesn't work because the columns which I am using to join the tables don't
> have the same name. How do i specify the columns if they don't have the same
> name?<br>

USING is just for the simple case where you want to join on identically-
named columns. (In the even simpler case where you want to join on all
identically-named columns, just say NATURAL JOIN.)

Whenever you need something more complicated, you say ON some-condition,
eg

SELECT '' AS "xxx", *
FROM J1_TBL LEFT OUTER JOIN J2_TBL ON j1_tbl.x = j2_tbl.y

Note that unlike USING and NATURAL, an ON-condition doesn't cause
columns to be omitted from the join result. So in the above example
you'd see both x and y as output columns.

> Poul L. Christiansen<br>
> <br>
> Tom Lane wrote:<br>
> <blockquote type=3D"cite" cite=3D"mid:6858(dot)976116402(at)sss(dot)pgh(dot)pa(dot)us"><pre wr=
> ap=3D"">"Poul L. Christiansen" <a class=3D"moz-txt-link-rfc2396E" href=3D"m=
> ailto:poulc(at)cs(dot)auc(dot)dk">&lt;poulc(at)cs(dot)auc(dot)dk&gt;</a> writes:<br></pre>
> <blockquote type=3D"cite"><pre wrap=3D"">But how do I make an outer join?=
> <br>What's the syntax?<br>I will RTFM if someone points me to the docs :-)=
> <br></pre></blockquote>
> <pre wrap=3D""><!----><br>I'm afraid I haven't updated the FM yet :-( .=
> .. but you could look at<br>the examples in the JOIN regress test, src/test=
> /regress/sql/join.sql.<br><br>Or, from the SQL92 spec, here's the grammar:<=
br> <br> &lt;from clause&gt; ::=3D FROM &lt;table reference&gt; [ { =
> &lt;comma&gt; &lt;table reference&gt; }... ]<br><br> &lt;table refe=
> rence&gt; ::=3D<br> &lt;table name&gt; [ [ AS ] &lt;correlat=
> ion name&gt;<br> [ &lt;left paren&gt; &lt;derived column=
> list&gt; &lt;right paren&gt; ] ]<br> | &lt;derived table&gt; =
> [ AS ] &lt;correlation name&gt;<br> [ &lt;left paren&gt;=
> &lt;derived column list&gt; &lt;right paren&gt; ]<br> | &lt;j=
> oined table&gt;<br><br> &lt;derived table&gt; ::=3D &lt;table subqu=
> ery&gt;<br><br> &lt;derived column list&gt; ::=3D &lt;column name l=
> ist&gt;<br><br> &lt;column name list&gt; ::=3D<br> &lt=
> ;column name&gt; [ { &lt;comma&gt; &lt;column name&gt; }...
> </blockquote>
> <br>
> </body></html>

BTW, would you PLEASE turn off the HTML option in your mail program?

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Christopher Kings-Lynne 2000-12-07 02:52:30 RE: AW: beta testing version
Previous Message Bruce Guenter 2000-12-07 00:56:04 Re: AW: beta testing version