Example:

You want a list of all customers and their outstanding orders, but they might
not have any orders at the moment:

SELECT c.name, o.ordernum, o.total
 FROM customers c LEFT OUTER JOIN orders o
   ON c.name = o.cust_name
 ORDER BY c.name, o.ordernum;

You don't want to omit a customer from the report just because they haven't
ordered something today.

Hi David,

I have most often used outer joins to bring in supplemental information to a
main query.  The way I think about it, outer joins let you incorporate
additional information into a main query WITHOUT affecting the main query
due to missing rows or values in the supplemental tables.

Here's a contrived example, but I think it illustrates well what I'm talking
about.  Let's say you have a human resources program that allows you to
track unlimited "write-ups" or warnings about employee behavioral problems.
The relevant tables, Employees and EmployWarnings, have the following
columns (among others):

 Employees: ID, LastName, FirstName
 EmployWarnings: EmployeeID, Date, Code, Summary

Date is the date of the warning, code is a four-character code that
classifies the warning, and Summary is a memo column with the text of the
write-up.

Now, say I want a list of all my employees, and I want that list to include
their last warning code and date.  The query for this would be

 SELECT LastName, FirstName, max(Date) as LastWarnDate, Code as LastWarnCode
 FROM Employee, EmployeeWarnings
 LEFT OUTER JOIN EmployeeID = ID
 GROUP BY LastName, FirstName

If I ran this query WITHOUT using an outer join, the results would NOT
include employees who had not been written up:

 SELECT LastName, FirstName, max(Date) as Date, Code
 FROM Employee, EmployeeWarnings
 WHERE EmployeeID = ID
 GROUP BY LastName, FirstName

Finally, here's a real example from a simple invoicing database I use.
Invoices can have optional service codes and dates associated with them.  To
generate a list of invoices numbered greater than 9000, with the minimum
and maximum service numbers associated with each invoice, I can use the
following query:

max(service.id) from invoice left outer join service on service.invoice_id =
invoice.id where invoice.id > 9000 group by invoice.id;
+-------+--------------+-----------------+-----------------+

+-------+--------------+-----------------+-----------------+

+-------+--------------+-----------------+-----------------+
7 rows in set (0.00 sec)

If I run it using "WHERE service.invoice_id = invoice.id" to do the join --
instead of "LEFT OUTER JOIN service ON service.invoice_id = invoice.id" --
then invoice 9004 doesn't show up.

Hope this helps.

Doug