Most joins return rows based on pairs of records from the two joined tables according to a given relationship. The outer join is different: All the rows from one table are returned, regardless of whether the relationship condition finds a matching row in the second table. When no corresponding record is found, columns that would otherwise contain values from the second table contain NULL. Outer joins are useful in producing reports when you do not want to exclude a record from the result if it does not have any corresponding data in a joined table. The following example uses an outer join to produce a report of the customer orders placed in the month of February only: mysql> SELECT c.name, o.order_date -> FROM customers c -> LEFT OUTER JOIN orders o -> ON o.customer_code = c.customer_code -> AND o.order_date BETWEEN '2006-02-01' AND '2006-02-28'; +-------------------------+------------+ | name | order_date | +-------------------------+------------+ | Presidents Incorporated | NULL | | Science Corporation | 2006-02-02 | | Science Corporation | 2006-02-05 | | Musicians of America | 2006-02-01 | | Musicians of America | 2006-02-02 | +-------------------------+------------+ 5 rows in set (0.00 sec) Because Presidents Incorporated placed no orders in the date range specifiedyou will see in the orders table that orders were placed only in Januarythe order date returned is NULL.
Exactly one row is returned for a customer record when there is no corresponding order data, whereas the other customers have multiple rows returned according to their actual order history. Notice that the filter on order_date is given in the ON clause, not the WHERE clause. Because the query returns a NULL order_date value for rows in customers that do have any orders, putting this filter in the WHERE clause would prevent those NULL values from being returned. |