db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Levitt (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-994) SQL examples for LEFT OUTER JOIN and RIGHT OUTER JOIN in the Derby Reference manual are incorrect
Date Sat, 18 Feb 2006 03:03:44 GMT
     [ http://issues.apache.org/jira/browse/DERBY-994?page=all ]

Jeff Levitt updated DERBY-994:
------------------------------

    Other Info: [Patch available]

> 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


Mime
View raw message