<< LEFT OUTER JOIN operation | CROSS JOIN operation >>

RIGHT OUTER JOIN operation

<< LEFT OUTER JOIN operation | CROSS JOIN operation >>

Derby Reference Manual

104

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'

Example 2

-- Join the EMPLOYEE and DEPARTMENT tables,
-- select the employee number (EMPNO),
-- employee surname (LASTNAME),
-- department number (WORKDEPT in the EMPLOYEE table
-- and DEPTNO in the DEPARTMENT table)
-- and department name (DEPTNAME)
-- of all employees who were born (BIRTHDATE) earlier than 1930

SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME
FROM SAMP.EMPLOYEE LEFT OUTER JOIN SAMP.DEPARTMENT
ON WORKDEPT = DEPTNO
AND YEAR(BIRTHDATE) < 1930

-- List every department with the employee number and
-- last name of the manager,
-- including departments without a manager

SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME
FROM DEPARTMENT LEFT OUTER JOIN EMPLOYEE
ON MGRNO = EMPNO

RIGHT OUTER JOIN operation

A RIGHT OUTER JOIN is one of the

JOIN operations

that allow you to specify a JOIN

clause. It preserves the unmatched rows from the second (right) table, joining them with
a NULL in the shape of the first (left) table. A LEFT OUTER JOIN B is equivalent to B
RIGHT OUTER JOIN A, with the columns in a different order.

Syntax

TableExpression

RIGHT [ OUTER ] JOIN

TableExpression

{
ON booleanExpression |

USING clause

}

The scope of expressions in the ON clause includes the current tables and any tables in
query blocks outer to the current SELECT. The ON clause can reference tables not being
joined and does not have to reference either of the tables being joined (though typically it
does).

Example 1

-- 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