Joe Celko's SQL for Smarties, 3rd Edition

17.2. OUTER JOINs

OUTER JOINs used to be done with proprietary vendor syntax. Today, the use of the Standard OUTER JOIN is universal. An OUTER JOIN is a JOIN that preserves all the rows in one or both tables, even when they do not have matching rows in the second table. Let’s take a real-world situation: I have a table of orders and a table of suppliers that I wish to JOIN for a report to tell us how much business we did with each supplier.

With an inner join, the query would be this:

SELECT Suppliers.sup_id, sup_name, order_nbr, order_amt
  FROM  Suppliers, Orders
 WHERE  Suppliers.sup_id = Orders.sup_id;

Some suppliers’ totals include credits for returned merchandise, and our total business with them works out to zero dollars. Other suppliers never ...