outer join

Also found in: Dictionary, Thesaurus, Legal.

outer join

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).
References in periodicals archive ?
With DataServer 9, we have implemented new features to support outer joins as well as performance enhancements based on a native locking implementation that greatly improves performance and query execution of the database.
All InfoMaker database interfaces (except Oracle) have been enhanced to provide support for ANSI SQL-92 outer join SQL syntax generation.
Extended outer join support -- BI/Query supports outer joins from more
Extended outer join support -- GQL supports outer joins from more
To further encourage embedded usage, Oracle has added a variety of SQL extensions to Personal Oracle Lite, including multi column indexing, outer join and read-only media support.
This feature can represent either equi-join of outer join, or a union operation.