db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4401) Document USING clause in joins
Date Fri, 09 Oct 2009 12:14:31 GMT

    [ 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.


Mime
View raw message