syntax of outer join in 7.1devel
<html><head></head><body>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 someone points me to the docs :-)<br>
<br>
Poul L. Christiansen<br>
<br>
<br>
</body></html>
"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
<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 someone points 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 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> ::= FROM <table reference> [ { <comma> <table reference> }... ]<br><br> <table reference> ::=<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> <column name> [ { <comma> <column name> }...
</blockquote>
<br>
</body></html>
"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 someone points 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 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> ::= FROM <table reference> [ { <comma> <table reference> }... ]<br><br> <table reference> ::=<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> <column name> [ { <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