Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.6.1.0
    • Fix Version/s: 10.6.1.0
    • Component/s: Documentation
    • Labels:
      None

      Description

      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.

      1. using.zip
        9 kB
        Knut Anders Hatlen
      2. using.diff
        8 kB
        Knut Anders Hatlen
      3. inner_syntax.diff
        1 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Open Open In Progress In Progress
          199d 17h 29m 1 Knut Anders Hatlen 26/Apr/10 08:50
          In Progress In Progress Resolved Resolved
          32m 38s 1 Knut Anders Hatlen 26/Apr/10 09:22
          Resolved Resolved Closed Closed
          4d 3h 22m 1 Knut Anders Hatlen 30/Apr/10 12:45
          Gavin made changes -
          Workflow jira [ 12478955 ] Default workflow, editable Closed status [ 12799672 ]
          Knut Anders Hatlen made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Hide
          Knut Anders Hatlen added a comment -

          The last cosmetic fix was committed to trunk right after the 10.6 branch was created. I have now merged it to the 10.6 branch and committed revision 938479.

          Show
          Knut Anders Hatlen added a comment - The last cosmetic fix was committed to trunk right after the 10.6 branch was created. I have now merged it to the 10.6 branch and committed revision 938479.
          Knut Anders Hatlen made changes -
          Attachment inner_syntax.diff [ 12442858 ]
          Hide
          Knut Anders Hatlen added a comment -

          Thanks, Kim! That's a good suggestion. The attached patch adds extra line breaks for INNER JOIN.
          Committed revision 938088.

          Show
          Knut Anders Hatlen added a comment - Thanks, Kim! That's a good suggestion. The attached patch adds extra line breaks for INNER JOIN. Committed revision 938088.
          Hide
          Kim Haase added a comment -

          This is terrific – thank you, Knut. The new USING clause topic is really clear and well-written.

          My only suggestion would be purely cosmetic – that the ON and USING part of the INNER JOIN syntax be put on separate lines to match the syntax in the LEFT OUTER JOIN and RIGHT OUTER JOIN topics (which you cleaned up nicely, I notice).

          Kim

          Show
          Kim Haase added a comment - This is terrific – thank you, Knut. The new USING clause topic is really clear and well-written. My only suggestion would be purely cosmetic – that the ON and USING part of the INNER JOIN syntax be put on separate lines to match the syntax in the LEFT OUTER JOIN and RIGHT OUTER JOIN topics (which you cleaned up nicely, I notice). Kim
          Knut Anders Hatlen made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Fix Version/s 10.6.0.0 [ 12313727 ]
          Resolution Fixed [ 1 ]
          Hide
          Knut Anders Hatlen added a comment -

          Committed revision 937958.

          Show
          Knut Anders Hatlen added a comment - Committed revision 937958.
          Knut Anders Hatlen made changes -
          Attachment using.zip [ 12442828 ]
          Attachment using.diff [ 12442829 ]
          Hide
          Knut Anders Hatlen added a comment -

          Here's a first stab at documentation for the USING clause. I added a new topic for it and pointed to it from the syntax descriptions for INNER JOIN, LEFT OUTER JOIN and RIGHT OUTER JOIN.

          I'll commit the patch shortly, so that at least some documentation for the USING clause makes it into the release candidate that's planned later today. Post-commit suggestions for improvements are welcome, though.

          Show
          Knut Anders Hatlen added a comment - Here's a first stab at documentation for the USING clause. I added a new topic for it and pointed to it from the syntax descriptions for INNER JOIN, LEFT OUTER JOIN and RIGHT OUTER JOIN. I'll commit the patch shortly, so that at least some documentation for the USING clause makes it into the release candidate that's planned later today. Post-commit suggestions for improvements are welcome, though.
          Knut Anders Hatlen made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Knut Anders Hatlen made changes -
          Assignee Knut Anders Hatlen [ knutanders ]
          Hide
          Knut Anders Hatlen added a comment -

          Some examples with toursdb:

          – Inner join that shows the number of cities in each country
          SELECT COUNTRY, COUNT AS NUM
          FROM COUNTRIES INNER JOIN CITIES USING (COUNTRY)
          GROUP BY COUNTRY ORDER BY NUM

          – Left outer join that shows the same as the previous join, but also includes countries with no cities in the result
          SELECT COUNTRY, COUNT(CITY_ID) AS NUM
          FROM COUNTRIES LEFT OUTER JOIN CITIES USING (COUNTRY)
          GROUP BY COUNTRY ORDER BY NUM

          Show
          Knut Anders Hatlen added a comment - Some examples with toursdb: – Inner join that shows the number of cities in each country SELECT COUNTRY, COUNT AS NUM FROM COUNTRIES INNER JOIN CITIES USING (COUNTRY) GROUP BY COUNTRY ORDER BY NUM – Left outer join that shows the same as the previous join, but also includes countries with no cities in the result SELECT COUNTRY, COUNT(CITY_ID) AS NUM FROM COUNTRIES LEFT OUTER JOIN CITIES USING (COUNTRY) GROUP BY COUNTRY ORDER BY NUM
          Hide
          Knut Anders Hatlen added a comment -

          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.

          Show
          Knut Anders Hatlen added a comment - 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.
          Knut Anders Hatlen made changes -
          Field Original Value New Value
          Link This issue blocks DERBY-4370 [ DERBY-4370 ]
          Knut Anders Hatlen created issue -

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Knut Anders Hatlen
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development