OUTER JOINS:

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 where we suspect data is missing or unknown.

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;

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