[ http://issues.apache.org/jira/browse/DERBY-994?page=comments#action_12367408 ]
Rajesh Kartha commented on DERBY-994: ------------------------------------- The patch looks good. If there is no further feedback, can one of the committers please commit it. > SQL examples for LEFT OUTER JOIN and RIGHT OUTER JOIN in the Derby Reference > manual are incorrect > ------------------------------------------------------------------------------------------------- > > Key: DERBY-994 > URL: http://issues.apache.org/jira/browse/DERBY-994 > Project: Derby > Type: Bug > Components: Documentation > Versions: 10.0.2.0 > Reporter: Rajesh Kartha > Fix For: 10.2.0.0 > Attachments: derby994.diff, rrefsqlj18922.html, rrefsqlj57522.html > > 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'; -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
