Thread: syntax of outer join in 7.1devel
Hi<br /><br /> I just grabbed the latest development version from the CVS, because i need the outer join functionality.<br/><br /> But how do I make an outer join? <br /><br /> What's the syntax?<br /><br /> I will RTFM if someonepoints me to the docs :-)<br /><br /> Poul L. Christiansen<br /><br /><br />
"Poul L. Christiansen" <poulc@cs.auc.dk> writes: > But how do I make an outer join? > What's the syntax? > I will RTFM if someone points me to the docs :-) I'm afraid I haven't updated the FM yet :-( ... but you could look at the examples in the JOIN regress test, src/test/regress/sql/join.sql. Or, from the SQL92 spec, here's the grammar: <from clause> ::= FROM <table reference> [ { <comma> <table reference> }... ] <table reference> ::= <table name> [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ] | <derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] | <joined table> <derived table> ::= <table subquery> <derived column list> ::= <column name list> <column name list> ::= <column name> [ { <comma> <column name> }... ] <joined table> ::= <cross join> | <qualified join> | <left paren> <joined table> <right paren> <cross join> ::= <table reference> CROSS JOIN <table reference> <qualified join> ::= <table reference> [ NATURAL ] [ <join type> ] JOIN <table reference> [ <join specification> ] <join specification> ::= <join condition> | <named columns join> <join condition> ::= ON <search condition> <named columns join> ::= USING <left paren> <join column list> <right paren> <join type> ::= INNER | <outer join type> [ OUTER ] | UNION <outer join type> ::= LEFT | RIGHT | FULL <join column list> ::= <column name list> We don't do UNION JOIN yet, but I think everything else shown in this snippet is implemented ... regards, tom lane
Using the example: SELECT '' AS "xxx", * FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i); ..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? Poul L. Christiansen Tom Lane wrote: <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> writes: But how do I make an outer join?= What's the syntax?I will RTFM if someone points me to the docs :-)= I'm afraid I haven't updated the FM yet :-( .= .. but you could look atthe examples in the JOIN regress test, src/test= /regress/sql/join.sql.Or, from the SQL92 spec, here's the grammar:<= br> <from clause> ::=3D FROM <table reference> [ { = <comma> <table reference> }... ] <table refe= rence> ::=3D <table name> [ [ AS ] <correlat= ion name> [ <left paren> <derived column= list> <right paren> ] ] | <derived table> = [ AS ] <correlation name> [ <left paren>= <derived column list> <right paren> ] | <j= oined table> <derived table> ::=3D <table subqu= ery> <derived column list> ::=3D <column name l= ist> <column name list> ::=3D <= ;column name> [ { <comma> <column name> }...
"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
See the updated FAQ on our web site for syntax. [ text/html is unsupported, treating like TEXT/PLAIN ] > <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> > <br> > Poul L. Christiansen<br> > <br> > Tom Lane wrote:<br> > <blockquote type="cite" cite="mid:6858.976116402@sss.pgh.pa.us"><pre wrap="">"Poul L. Christiansen" <a class="moz-txt-link-rfc2396E"href="mailto:poulc@cs.auc.dk"><poulc@cs.auc.dk></a> writes:<br></pre> > <blockquote type="cite"><pre wrap="">But how do I make an outer join? <br>What's the syntax?<br>I will RTFM if someonepoints me to the docs :-)<br></pre></blockquote> > <pre wrap=""><!----><br>I'm afraid I haven't updated the FM yet :-( ... but you could look at<br>the examples in theJOIN regress test, src/test/regress/sql/join.sql.<br><br>Or, from the SQL92 spec, here's the grammar:<br><br> <from clause> ::= FROM <table reference> [ { <comma> <table reference> }... ]<br><br> <tablereference> ::=<br> <table name> [ [ AS ] <correlation name><br> [ <left paren> <derived column list> <right paren> ] ]<br> | <derived table> [ AS ]<correlation name><br> [ <left paren> <derived column list> <right paren> ]<br> | <joined table><br><br> <derived table> ::= <table subquery><br><br> <derived column list> ::= <column name list><br><br> <column name list> ::=<br> <columnname> [ { <comma> <column name> }... > </blockquote> > <br> > </body></html> > > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026