Derby
  1. Derby
  2. DERBY-4374

Document the CROSS JOIN operation in the reference manual

    Details

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

      Description

      CROSS JOIN was added in DERBY-4355. We should document it alongside the other join operations in the reference manual.

      1. DERBY-4374.diff
        6 kB
        Kim Haase
      2. DERBY-4374.stat
        0.1 kB
        Kim Haase
      3. DERBY-4374.zip
        6 kB
        Kim Haase
      4. DERBY-4374-2.diff
        6 kB
        Kim Haase
      5. DERBY-4374-2.zip
        6 kB
        Kim Haase
      6. DERBY-4374-3.diff
        6 kB
        Kim Haase
      7. DERBY-4374-3.zip
        7 kB
        Kim Haase

        Issue Links

          Activity

          Hide
          Kim Haase added a comment -

          Changes have appeared in Latest Alpha Manuals, so closing.

          Show
          Kim Haase added a comment - Changes have appeared in Latest Alpha Manuals, so closing.
          Hide
          Kim Haase added a comment -

          Thanks, Knut, for all your help.

          Committed patch DERBY-4374-3.diff to documentation trunk at revision 821180.

          Show
          Kim Haase added a comment - Thanks, Knut, for all your help. Committed patch DERBY-4374 -3.diff to documentation trunk at revision 821180.
          Hide
          Knut Anders Hatlen added a comment -

          The latest patch looks great! +1 to commit.

          I used a debug build when I ran the queries, which hopefully explains why it was slower. I'll look more into it, and I'll also file a bug against the optimizer for its inability to optimize the inner/cross join variants.

          Show
          Knut Anders Hatlen added a comment - The latest patch looks great! +1 to commit. I used a debug build when I ran the queries, which hopefully explains why it was slower. I'll look more into it, and I'll also file a bug against the optimizer for its inability to optimize the inner/cross join variants.
          Hide
          Kim Haase added a comment -

          Thanks again, Knut – here is another patch (DERBY-4374-3.diff, DERBY-4374-3.zip) with these changes. Hope it's okay.

          You're right that the inner join version is slower than the (SELECT * FROM FLIGHTS, COUNTRIES) S version (I am using 10.5.3 so I can't try the cross join myself) – though it doesn't take 3 minutes by any means, more like 30 seconds vs. instantaneous. I wonder if something has happened since 10.5.3 to slow it down even more.

          Show
          Kim Haase added a comment - Thanks again, Knut – here is another patch ( DERBY-4374 -3.diff, DERBY-4374 -3.zip) with these changes. Hope it's okay. You're right that the inner join version is slower than the (SELECT * FROM FLIGHTS, COUNTRIES) S version (I am using 10.5.3 so I can't try the cross join myself) – though it doesn't take 3 minutes by any means, more like 30 seconds vs. instantaneous. I wonder if something has happened since 10.5.3 to slow it down even more.
          Hide
          Knut Anders Hatlen added a comment -

          The CROSS JOIN operator can be replaced with an INNER JOIN where the join clause always evaluates to true, e.g. 1=1. It can also be replaced with a sub-query. So equivalent queries will be:

          INNER JOIN:

          SELECT * FROM CITIES LEFT OUTER JOIN
          FLIGHTS INNER JOIN COUNTRIES ON 1=1
          ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
          WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'

          Sub-query:

          SELECT * FROM CITIES LEFT OUTER JOIN
          (SELECT * FROM FLIGHTS, COUNTRIES) S
          ON CITIES.AIRPORT = S.ORIG_AIRPORT
          WHERE S.COUNTRY_ISO_CODE = 'US'

          (Curiously, the queries that use CROSS JOIN or INNER JOIN need more than three minutes to complete on the toursdb database, whereas the sub-query variant completes in less than ten seconds. Probably an optimizer issue, but at least the results are the same.)

          Show
          Knut Anders Hatlen added a comment - The CROSS JOIN operator can be replaced with an INNER JOIN where the join clause always evaluates to true, e.g. 1=1. It can also be replaced with a sub-query. So equivalent queries will be: INNER JOIN: SELECT * FROM CITIES LEFT OUTER JOIN FLIGHTS INNER JOIN COUNTRIES ON 1=1 ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US' Sub-query: SELECT * FROM CITIES LEFT OUTER JOIN (SELECT * FROM FLIGHTS, COUNTRIES) S ON CITIES.AIRPORT = S.ORIG_AIRPORT WHERE S.COUNTRY_ISO_CODE = 'US' (Curiously, the queries that use CROSS JOIN or INNER JOIN need more than three minutes to complete on the toursdb database, whereas the sub-query variant completes in less than ten seconds. Probably an optimizer issue, but at least the results are the same.)
          Hide
          Kim Haase added a comment -

          Thanks, Knut! I'm attaching a revised patch (DERBY-4374-2.diff, DERBY-4374-2.zip).

          It seemed simpler to just revise the example using the format you suggested, since it is basically the same except for the parentheses. Is there a way to perform the same select without using a CROSS JOIN? If so, we could add that, since the other example pairs show that contrast.

          Show
          Kim Haase added a comment - Thanks, Knut! I'm attaching a revised patch ( DERBY-4374 -2.diff, DERBY-4374 -2.zip). It seemed simpler to just revise the example using the format you suggested, since it is basically the same except for the parentheses. Is there a way to perform the same select without using a CROSS JOIN? If so, we could add that, since the other example pairs show that contrast.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks Kim! The docs look great.

          I'm wondering if we should add a note about the ON clause to the more complex example. Users may be confused since we say that CROSS JOIN doesn't let you specify a join clause (aka ON/USING), whereas the example appears to be showing just that; a CROSS JOIN with an ON clause. What about adding another example below it and say something like


          Note that the ON clause in the example above is associated with the LEFT OUTER JOIN operator, not with the CROSS JOIN operator, and it is therefore equivalent to the following statement:

          SELECT * FROM CITIES LEFT OUTER JOIN
          (FLIGHTS CROSS JOIN COUNTRIES)
          ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT
          WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'


          The parentheses will hopefully make it clearer how the previous example could be correct even if it apparently had a CROSS JOIN + ON, and it'll additionally show that parens are allowed around the join operations.

          Show
          Knut Anders Hatlen added a comment - Thanks Kim! The docs look great. I'm wondering if we should add a note about the ON clause to the more complex example. Users may be confused since we say that CROSS JOIN doesn't let you specify a join clause (aka ON/USING), whereas the example appears to be showing just that; a CROSS JOIN with an ON clause. What about adding another example below it and say something like Note that the ON clause in the example above is associated with the LEFT OUTER JOIN operator, not with the CROSS JOIN operator, and it is therefore equivalent to the following statement: SELECT * FROM CITIES LEFT OUTER JOIN (FLIGHTS CROSS JOIN COUNTRIES) ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US' The parentheses will hopefully make it clearer how the previous example could be correct even if it apparently had a CROSS JOIN + ON, and it'll additionally show that parens are allowed around the join operations.
          Hide
          Kim Haase added a comment -

          Attaching DERBY-4374.diff, DERBY-4374.stat, and DERBY-4374.zip, with a new CROSS JOIN topic and with updates to the "JOIN operations" and keywords topics.

          I changed the examples to use one of the demo databases, but they seem to me to be rather bogus – if you have any suggestions for improving them, I'd be grateful.

          Show
          Kim Haase added a comment - Attaching DERBY-4374 .diff, DERBY-4374 .stat, and DERBY-4374 .zip, with a new CROSS JOIN topic and with updates to the "JOIN operations" and keywords topics. I changed the examples to use one of the demo databases, but they seem to me to be rather bogus – if you have any suggestions for improving them, I'd be grateful.
          Hide
          Knut Anders Hatlen added a comment -

          We also need to add CROSS to the list of SQL reserved words in the reference manual.

          Show
          Knut Anders Hatlen added a comment - We also need to add CROSS to the list of SQL reserved words in the reference manual.
          Hide
          Knut Anders Hatlen added a comment -

          The description of the operation could say something similar to this (inspired by the wording used in the other JOIN operators):

          A CROSS JOIN is a JOIN operation that produces the Cartesian product of two tables. Unlike other JOIN operators, it does not let you specify a join clause. You may however specify a WHERE clause in the SELECT statement.

          Examples that we may provide:

          SELECT * FROM T1 CROSS JOIN T2
          which is equivalent to
          SELECT * FROM T1, T2

          and

          SELECT * FROM T1 CROSS JOIN T2 WHERE T1.X = T2.X
          which is equivalent to
          SELECT * FROM T1 INNER JOIN T2 ON T1.X = T2.X

          And perhaps we should have a more complex example. For instance:

          SELECT * FROM T1 LEFT OUTER JOIN T2 CROSS JOIN T3 ON T1.X = T2.X WHERE T3.Z > 50

          We should probably change the examples so that they use the same table names as the other JOIN examples.

          Show
          Knut Anders Hatlen added a comment - The description of the operation could say something similar to this (inspired by the wording used in the other JOIN operators): A CROSS JOIN is a JOIN operation that produces the Cartesian product of two tables. Unlike other JOIN operators, it does not let you specify a join clause. You may however specify a WHERE clause in the SELECT statement. Examples that we may provide: SELECT * FROM T1 CROSS JOIN T2 which is equivalent to SELECT * FROM T1, T2 and SELECT * FROM T1 CROSS JOIN T2 WHERE T1.X = T2.X which is equivalent to SELECT * FROM T1 INNER JOIN T2 ON T1.X = T2.X And perhaps we should have a more complex example. For instance: SELECT * FROM T1 LEFT OUTER JOIN T2 CROSS JOIN T3 ON T1.X = T2.X WHERE T3.Z > 50 We should probably change the examples so that they use the same table names as the other JOIN examples.
          Hide
          Knut Anders Hatlen added a comment -

          The syntax description should say:

          TableExpression CROSS JOIN

          { TableViewOrFunctionExpression | ( TableExpression ) }
          Show
          Knut Anders Hatlen added a comment - The syntax description should say: TableExpression CROSS JOIN { TableViewOrFunctionExpression | ( TableExpression ) }

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development