outer join

outer join
full outer join
left join
left outer join
right join
right outer join

   <database> A less commonly used variant of the {inner join}
   {relational database} operation.  An inner join selects rows
   from two {tables} such that the value in one {column} of the
   first table also appears in a certain column of the second
   table.  For an outer join, the result also includes all rows
   from the first operand ("left outer join"), or the second
   operand ("right outer join"), or both ("full outer join").  A
   field in a result row will be null if the corresponding input
   table did not contain a matching row.

   For example, if we want to list all employees and their
   employee number, but not all employees have a number, then we
   could say (in {SQL-92} syntax, as used by {Microsoft SQL
   Server}):

   	SELECT employee.name, empnum.number
   	FROM employee
   	LEFT JOIN empnum ON employee.id = empnum.id

   or, in {Sybase} syntax:

   	SELECT employee.name, empnum.number
   	FROM employee, empnum
   	WHERE employee.id *= empnum.id

   The "*" on the left means "left outer join".  "*=*" would be a
   full outer join.

   In {Oracle} syntax:

   	SELECT employee.name, empnum.number
   	FROM employee, empnum
   	WHERE employee.id = empnum.id (+)

   Note that the "(+)" on the right means "left outer join".

   These all mean that all rows from the left-hand "employee"
   table will appear in the result, even if there is no match for
   their ID in the empnum table.  Where there is no empnum.id
   equal to a given employee.id, a result row is output anyway
   but with all result columns from the empnum table null
   (empnum.number in this case).

   (2004-11-12)