Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1503322929.1255090471291.JavaMail.jira@brutus>
Date: Fri, 9 Oct 2009 05:14:31 -0700 (PDT)
From: "Knut Anders Hatlen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-4401) Document USING clause in joins
In-Reply-To: <1330314536.1255011631290.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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