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 |
USING clause
}
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
