UTEP Oracle 9i
UTEP Oracle 10g Notes

Difference

Understanding the concept:

While the intersection of two recordsets is used to "find the duplicates," the difference operator will "find the orphans." The relational difference of two recordsets is the records that belong to one recordset but not the other.

For example, given the same two recordsets shown below,

the SELECT statement below will return the unmatched records:

SELECT DuplicateCustomers1.*

FROM DuplicateCustomers1 

LEFT JOIN DuplicateCustomers2 

ON (DuplicateCustomers1.CustomerID = DuplicateCustomers2.CustomerID) 

AND (DuplicateCustomers1.CompanyName = DuplicateCustomers2.CompanyName)

WHERE (DuplicateCustomers2.CustomerID IS NULL);

The outer join operation in this statement returns all the records from the two lists. As you will recall, an outer join supplies Null for the fields that do not have a match in the other table. The WHERE clause uses the IS NULL operator to restrict the records returned to only those (unmatched) records.

Try performing the operation in two discrete steps: first create the outer join as a view, and then restrict the view with the WHERE statement. The result showed below: