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

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

#### Example 2

    -- a TableExpression can be a joinOperation. Therefore
    -- you can have multiple join operations in a FROM clause
    -- List every employee number and last name
    -- with the employee number and last name of their manager

    SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME
    	FROM EMPLOYEE E RIGHT OUTER JOIN
    	DEPARTMENT RIGHT OUTER JOIN EMPLOYEE M
            ON MGRNO = M.EMPNO
            ON E.WORKDEPT = DEPTNO

Parent topic: Statements
Related concepts
Interaction with the dependency system
CREATE statements
DROP Statements
RENAME statements
SET statements
Related reference
ALTER TABLE statement
GRANT statement
REVOKE statement
CALL (PROCEDURE)
CONSTRAINT clause
DECLARE GLOBAL TEMPORARY TABLE statement
DELETE statement
FOR UPDATE clause
FROM clause
GROUP BY clause
HAVING clause
INNER JOIN
INSERT statement
JOIN operation
LEFT OUTER JOIN
LOCK TABLE statement
ORDER BY clause
Query
ScalarSubquery
SelectExpression
SELECT statement
TableExpression
TableSubquery
UPDATE statement
VALUES expression
WHERE clause
WHERE CURRENT OF clause