SQL-J Language Reference
Page 44 of 121

RIGHT OUTER JOIN

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 ( SimpleColumnName [ , SimpleColumnName]* )
}

The scope of expressions in either the ON or the USING 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). For additional differences between ON and USING clauses, see ResultSet and Performance Differences Between ON and USING.

RIGHT OUTER JOIN Examples

-- get all countries and corresponding cities, including
-- countries without any cities
SELECT city.getName(), country
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 city, country
FROM Cities RIGHT OUTER JOIN Countries
USING (country_ISO_code)
WHERE Countries.region = 'Africa'

-- a TableExpression can be a joinOperation. Therefore
-- you can have multiple join operations in a FROM clause
SELECT country, city.getName(), hotel_name, normal_rate
FROM Cities RIGHT OUTER JOIN Countries
ON Countries.country_ISO_code=Cities.country_ISO_code
INNER JOIN Hotels
ON Cities.city_id=Hotels.city_id