outer join

Also found in: Dictionary, Thesaurus.

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).
This article is provided by FOLDOC - Free Online Dictionary of Computing (foldoc.org)


In relational database management, to match one table (file) against another based on some condition creating a third table with data from the matching tables. For example, a customer table can be joined with an order table creating a table for all customers who purchased a particular product.

The default type of join is known as an "inner" join. It produces a resulting record if there is a matching condition. For example, matching shipments with receipts would produce only those shipments that have been received. On the other hand, an "outer" join using that example would create a record for every shipment whether or not it was received. The data for received items would be attached to the shipments, and empty, or null, fields would be attached to shipments without receipts.

A Simple Join
This example matches the sales table against the product table based on product number to derive the product description.
Copyright © 1981-2019 by The Computer Language Company Inc. All Rights reserved. THIS DEFINITION IS FOR PERSONAL USE ONLY. All other reproduction is strictly prohibited without permission from the publisher.
References in periodicals archive ?
While some analogies did not accurately portray the SQL term and contained logic error, Table 8 highlights six noteworthy student analogies related to SubQuery and Full Outer Join. Examples were selected to illustrate creativity, variety, and analogies that effectively described the SQL concepts in vivid detail.
where F(d) is the right branch of the outer join in Rule (O5):
Here is a query that joins orders and customers; it uses an outer join to include the customers who have placed no orders.