db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rajesh Kartha (JIRA)" <derby-...@db.apache.org>
Subject [jira] Created: (DERBY-994) SQL examples for LEFT OUTER JOIN and RIGHT OUTER JOIN in the Derby Reference manual are incorrect
Date Thu, 16 Feb 2006 06:24:01 GMT
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


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