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

Examples

--match cities to countries 
SELECT CITIES.COUNTRY, REGION 
FROM Countries 
LEFT OUTER JOIN Cities
ON CITY_ID=CITY_ID
WHERE REGION = 'Asia';
 -- use the synonymous syntax, RIGHT JOIN, to achieve exactly 
-- the same results as in the example above 
 
SELECT COUNTRIES.COUNTRY, REGION 
FROM Countries 
LEFT JOIN Cities
ON CITY_ID=CITY_ID;
 -- 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;