Outer Join

By Robert Laing

As explained in inner join relational algebra uses various bowtie symbols to describe binary operations on two tables, which are anologous to combining two sets as I’ll try to explain in this diagram.

The P ∩ Q portion could be considered inner join P ⋈ Q. A left outer join P ⟕ Q is (P - Q) ∪ (P ∩ Q), a right outer join P ⟖ Q is (P ∩ Q) ∪ (Q - P), and a full outer join P ⟗ Q is (P - Q) ∪ (P ∩ Q) ∪ (Q - P).

To be able to use relations — tables — as sets, we need to project both to the same columns, which as explained in fill values can create problems for the unwary.

Left Outer Join

Now I want to modify the Three-way inner join example to include {456, 567, 789, 654}. This can be done with a three-way left outer join

Student ⟕ Apply ⟕ College

SELECT sID, sName, GPA, sizeHS, major, decision, College.cName, state, enrollment
FROM Student LEFT OUTER JOIN Apply USING(sID) LEFT OUTER JOIN College USING(cName)
ORDER BY sID;

Full Outer Join

Lets insert a row into the apply table with no matching sID in the student table and cName in the College table:

INSERT INTO Apply VALUES (321, 'Harvard', 'history', 'N');

A reminder that USING allows us to dispense with tablename.shared_column syntax, which would cause unwanted NULL values in those respected columns otherwise.

SELECT sID, sName, GPA, sizeHS, major, decision, cName, state, enrollment
FROM Student FULL OUTER JOIN Apply USING(sID) FULL OUTER JOIN College USING(cName)
ORDER BY sID;

A synonymous way to do that is

SELECT sID, sName, GPA, sizeHS, major, decision, cName, state, enrollment
FROM Student NATURAL FULL OUTER JOIN Apply NATURAL FULL OUTER JOIN College
ORDER BY sID;

Don’t forget to do delete the added row to get the database back to the starting state:

DELETE FROM Apply WHERE sID = 321;