outer join


outer join

(database)A less commonly used variant of the inner joinrelational database operation. An inner join selects rowsfrom two tables such that the value in one column of thefirst table also appears in a certain column of the secondtable. For an outer join, the result also includes all rowsfrom the first operand ("left outer join"), or the secondoperand ("right outer join"), or both ("full outer join"). Afield in a result row will be null if the corresponding inputtable did not contain a matching row.

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

SELECT employee.name, empnum.numberFROM employeeLEFT JOIN empnum ON employee.id = empnum.id

or, in Sybase syntax:

SELECT employee.name, empnum.numberFROM employee, empnumWHERE employee.id *= empnum.id

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

In Oracle syntax:

SELECT employee.name, empnum.numberFROM employee, empnumWHERE 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 fortheir ID in the empnum table. Where there is no empnum.idequal to a given employee.id, a result row is output anywaybut with all result columns from the empnum table null(empnum.number in this case).