OUTER JOINS:

Used to find data in one table that is missing related data from another, for example a supplier from whom we have no products, or a product that hasn't been categorized, or a customer who has not placed an order.

Principle: Join the tables and find all the rows from one table whose corresponding rows in the other table have a null value (data missing or value unknown).

Example/exercise

List the company name, contact person, and phone number of customers who have not placed orders. Type the following statement in the SQL window:

SELECT CompanyName, ContactName, Phone

FROM Customers

     LEFT JOIN Orders

     ON Customers.CustomerID = Orders.CustomerID

WHERE Orders.CustomerID is null;

The result should be 2 rows.

In MS Access, this statement is similar to the SQL generated by the Unmatched Query Wizard.

Note the emphasis on "related data" above.   See whether an outer join is necessary to create a list like the one in the previous example of customers whose orders have not been shipped.  Why or why not?