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
{
    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
splice> 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
splice> 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 born (BIRTHDATE) earlier than 1930
splice> 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
splice> SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME
  FROM DEPARTMENT LEFT OUTER JOIN EMPLOYEE
  ON MGRNO = EMPNO;

See Also