Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
10.0.2.0
-
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';