Re: syntax of outer join in 7.1devel

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: syntax of outer join in 7.1devel
Дата
Msg-id 20409.976154139@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: syntax of outer join in 7.1devel  ("Poul L. Christiansen" <poulc@cs.auc.dk>)
Список pgsql-general
Дерево обсуждения 
"Poul L. Christiansen" <poulc@cs.auc.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.976116402@sss.pgh.pa.us"><pre wr=
> ap=3D"">"Poul L. Christiansen" <a class=3D"moz-txt-link-rfc2396E" href=3D"m=
> ailto:poulc@cs.auc.dk"><poulc@cs.auc.dk></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>         <from clause> ::=3D FROM <table reference> [ { =
> <comma> <table reference> }... ]<br><br>         <table refe=
> rence> ::=3D<br>                <table name> [ [ AS ] <correlat=
> ion name><br>                    [ <left paren> <derived column=
>  list> <right paren> ] ]<br>              | <derived table> =
> [ AS ] <correlation name><br>                    [ <left paren>=
>  <derived column list> <right paren> ]<br>              | <j=
> oined table><br><br>         <derived table> ::=3D <table subqu=
> ery><br><br>         <derived column list> ::=3D <column name l=
> ist><br><br>         <column name list> ::=3D<br>              <=
> ;column name> [ { <comma> <column name> }...
>     </blockquote>
>     <br>
> </body></html>

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

            regards, tom lane

В списке pgsql-general по дате отправления: