Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-994

SQL examples for LEFT OUTER JOIN and RIGHT OUTER JOIN in the Derby Reference manual are incorrect

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.0
    • Fix Version/s: 10.1.3.1
    • Component/s: Documentation
    • Labels:
      None

      Description

      The SQL examples for the following in the reference manual are wrong:

      • LEFT OUTER JOIN
      • RIGHT OUTER JOIN

      The incorrectness are pointed out below. Also the fix (correct sql and descriptions) that should replace these are provided. Can someone please review this and commit into the codeline.

      LEFT OUTER JOIN :
      ================
      (v10.1) http://db.apache.org/derby/docs/10.1/ref/rrefsqlj18922.html
      (trunk) http://db.apache.org/derby/docs/dev/ref/rrefsqlj18922.html

      The manual shows:

      <quote>

      --match cities to countries

      [wrong description: should mention cities to countries in Asia] <==

      SELECT CITIES.COUNTRY, REGION
      FROM Countries
      LEFT OUTER JOIN Cities
      ON CITY_ID=CITY_ID
      WHERE REGION = 'Asia';

      [wrong sql: This will return 1305 rows meaningless rows] <==

      – use the synonymous syntax, RIGHT JOIN, to achieve exactly
      – the same results as in the example above

      [wrong description: The synonymous syntax is LEFT JOIN] <==

      SELECT COUNTRIES.COUNTRY, REGION
      FROM Countries
      LEFT JOIN Cities
      ON CITY_ID=CITY_ID;

      [wrong sql: Returns a Cartesian product of the two tables: 9918 rows selected] <==

      </quote>

      The correct description and sql for LEFT OUTER JOIN should be:
      ---------------------------------------------------------------------------------------

      --match cities to countries in Asia
      SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME,REGION
      FROM COUNTRIES
      LEFT OUTER JOIN CITIES
      ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
      WHERE REGION='Asia';

      – use the synonymous syntax, LEFT JOIN, to achieve exactly
      – the same results as in the example above

      SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME,REGION
      FROM COUNTRIES
      LEFT JOIN CITIES
      ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
      WHERE REGION='Asia';

      [Both the above queries will return
      COUNTRY |CITY_NAME |REGION
      ------------------------------------------------------------------------------
      Afghanistan |Kabul |Asia
      Bangladesh |NULL |Asia
      Cambodia |NULL |Asia
      China |Hong Kong |Asia
      China |Shanghai |Asia
      India |Bombay |Asia
      India |Calcutta |Asia
      Indonesia |Jakarta |Asia
      Japan |Osaka |Asia
      Japan |Tokyo |Asia
      Korea, Republic of |Seoul |Asia
      Malaysia |NULL |Asia
      Nepal |NULL |Asia
      Philippines |Manila |Asia
      Singapore |Singapore |Asia
      Sri Lanka |NULL |Asia
      Thailand |NULL |Asia
      Viet Nam |NULL |Asia

      18 rows selected] <==

      RIGHT OUTER JOIN:
      =================
      (v10.1) http://db.apache.org/derby/docs/10.1/ref/rrefsqlj57522.html
      (trunk) http://db.apache.org/derby/docs/dev/ref/rrefsqlj57522.html

      The manual shows:

      <quote>

      – get all countries and corresponding cities, including
      – countries without any cities
      SELECT CITY_NAME, CITIES.COUNTRY
      FROM CITIES RIGHT OUTER JOIN COUNTRIES
      ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE;

      [wrong sql: Return meaningless 156 rows ] <==

      – get all countries in Africa and corresponding cities, including
      – countries without any cities
      SELECT CITY_NAME, CITIES.COUNTRY
      FROM CITIES RIGHT OUTER JOIN COUNTRIES
      ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE;
      WHERE Countries.region = 'frica';

      [wrong sql:
      1) 'frica' is incorrect in the WHERE clause
      2) incorrect results with NULL country values
      3) incorrect ';' before WHERE clause] <==

      – use the synonymous syntax, RIGHT JOIN, to achieve exactly
      – the same results as in the example above
      SELECT CITY_NAME, CITIES.COUNTRY
      FROM CITIES RIGHT JOIN COUNTRIES
      ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
      WHERE Countries.region = 'Africa';

      [wrong sql: Incorrect results with NULL country values] <==

      </quote>

      The correct description and sql for RIGHT OUTER JOIN should be:
      ------------------------------------------------------------------------------------------

      – get all countries and corresponding cities, including
      – countries without any cities

      SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME
      FROM CITIES
      RIGHT OUTER JOIN COUNTRIES
      ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE;

      – get all countries in Africa and corresponding cities, including
      – countries without any cities

      SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME
      FROM CITIES
      RIGHT OUTER JOIN COUNTRIES
      ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
      WHERE Countries.region = 'Africa';

      – use the synonymous syntax, RIGHT JOIN, to achieve exactly
      – the same results as in the example above

      SELECT COUNTRIES.COUNTRY, CITIES.CITY_NAME
      FROM CITIES
      RIGHT JOIN COUNTRIES
      ON CITIES.COUNTRY_ISO_CODE = COUNTRIES.COUNTRY_ISO_CODE
      WHERE Countries.region = 'Africa';

        Attachments

        1. derby994.diff
          9 kB
          Jeff Levitt
        2. rrefsqlj18922.html
          8 kB
          Jeff Levitt
        3. rrefsqlj57522.html
          8 kB
          Jeff Levitt

          Activity

            People

            • Assignee:
              jlevitt Jeff Levitt
              Reporter:
              kartha Rajesh Kartha
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: