# LEFT OUTER JOIN

A LEFT OUTER JOIN is one of the JOIN operations that allow you to specify a join clause. It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.

#### Syntax

    TableExpression LEFT [ OUTER ] JOIN TableExpression
    {
        ON booleanExpression
        }

The scope of expressions in either 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

    --match cities to countries in Asia

    SELECT CITIES.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'

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

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
LOCK TABLE statement
ORDER BY clause
Query
RIGHT OUTER JOIN
ScalarSubquery
SelectExpression
SELECT statement
TableExpression
TableSubquery
UPDATE statement
VALUES expression
WHERE clause
WHERE CURRENT OF clause