The outer join operation is an extension of the inner join operation that includes all the tuples in one relation even when these have no matching tuples on the second relation. Since the scheme of the result will include attributes from the second relation, there will be null values on these attributes whenever a tuple from the first relation has no matching tuple in the second relation.
The outer join of two relations can be interpreted as follows:
Recall that the most common join operation is between two tables that are related via a FK-PK. In this situation, the result of an outer join makes sense only if at least one side of the relationship has an optional participation. Otherwise, the result of the outer join is equivalent to that of the inner join. For example, the result set of an outer join between Depositors and Accounts in the banking database is equivalent to the inner join result set -- that's because every account must have at least one depositor and a depositor tuple can only exist if there is an account associated with it. Similarly with the Orders and OrderLines relations in the database example on Tom Jewett's website.
In the ToyBank database, we have a relationships between the Customers relationship and the Loans relationship. In that relationship, each customer may own many loans and each loan must be owned by at most one customer. There is a minimum participation of the customer in the owns relationship, so an outer join is meaningful in that it will give a different result than just an inner join.
To see the results of the SQL statements given on this page, you should run them on the ToyBank database that has been distributed in this website.
There are three outer joins: the left outer join (LOJ), the right outer join (ROJ), and the full outer join (FOJ). Their semantics are as explained above, the difference between them is the relation whose tuples are kept even if they do not match tuples in the other relation.
You should note the following equivalences:
In a left outer join operation, the result set will contain every tuple from the relation to the left of the operator and when one such tuple does not match any tuple on the relation to the right of the operator, then null values are used on the columns corresponding to that relation. For example:
SELECT id, fname, lname, loan_number, amount
FROM customers LEFT OUTER JOIN loans ON id=owner_id;
In a right outer join operation, the result set will contain every tuple from the relation to the right of the operator and when one such tuple does not match any tuple on the relation to the left of the operator, then null values are used on the columns corresponding to that relation. For example:
SELECT
id, fname, lname, loan_number, amount
FROM customers RIGHT OUTER JOIN loans ON id=owner_id;
In a FULL outer join operation, the result set will contain every tuple from both relations and when a tuple from either relation does not match any tuples on the second relation relation, then null values are used on the columns corresponding to that second relation. While the ANSI SQL syntax does call for the use of the keywords FULL OUTER JOIN, some database systems do not support it and one must perform a set union of the left and right outer joins. For example:
SELECT id, fname, lname, loan_number, amount
FROM customers LEFT OUTER JOIN loans ON id=owner_id
UNION
SELECT id, fname, lname, loan_number, amount
FROM customers RIGHT OUTER JOIN loans ON id=owner_id;
Outer joins are useful whenever queries require that all of the information be taken into account, not just that information that is formed by the matching of tuples. For example, when computing aggregate functions, you may need to report aggregate function values for unmatched tuples.
For every customer,find the number of loans she owns -- note that this must report zero for those customers without any loans:
SELECT id, fname, lname, COUNT(loan_number)
FROM customers LEFT OUTER JOIN loans ON id=owner_id
GROUP BY id, fname, lname;