[
https://issues.apache.org/jira/browse/DERBY-4401?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12763986#action_12763986
]
Knut Anders Hatlen commented on DERBY-4401:
-------------------------------------------
Some things we could mention about USING (not sure about exact wording or
placement):
- USING can be used instead of ON if the columns that are to be matched have
the same name in both tables, and the comparison operator is =. Ex. ON T1.A =
T2.A AND T1.B = T2.B --> USING (A, B).
- If the select list is *, the columns returned from T1 JOIN T2 USING (...) is
the columns in the USING clause, followed by all the columns of T1 not
referenced in USING, followed by all the columns of T2 not referenced in USING.
- If the select list contains T1.*, it will be expanded to all columns in T1
not referenced in USING. Same for T2.*.
- Columns in USING can be referenced without qualification. So given two tables
T1(A,B) and T2(A,B), this is allowed:
SELECT A FROM T1 JOIN T2 USING (A)
whereas this is disallowed since the column B is not mentioned in USING and
it's ambiguous which B to use:
SELECT B FROM T1 JOIN T2 USING (A)
Formally, the references to a join column A are equivalent to COALESCE(T1.A,
T2.A). For inner joins, T1.A and T2.A will always be equal, but for outer joins
NULLs may be filled in on one of the sides so that they are not necessarily
equal. You can always specify which of the A's you want by qualifying it with
the table name.
> Document USING clause in joins
> ------------------------------
>
> Key: DERBY-4401
> URL: https://issues.apache.org/jira/browse/DERBY-4401
> Project: Derby
> Issue Type: Improvement
> Components: Documentation
> Affects Versions: 10.6.0.0
> Reporter: Knut Anders Hatlen
> Priority: Minor
>
> DERBY-4370 made some of the join operations accept a USING clause. This
> should be documented in the reference manual.
> The JOIN operations section talks about "join clause", which should be fine
> since that could mean both ON clause and USING clause.
> The sections INNER JOIN operation, LEFT OUTER JOIN operation and RIGHT OUTER
> JOIN operation need to be updated with the new syntax. We need to replace
> {
> ON booleanExpression
> }
> with
> {
> { ON booleanExpression } |
> { USING ( Simple-column-Name [ , Simple-column-Name ]* ) }
> }
> Perhaps it would make sense to factor out this part of the syntax into a
> separate element JoinSpecification, and explain the meaning of USING there.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
