When it is necessary to join an outer table that contains general information and an inner table that contains partial information to obtain information on all rows of the outer table, in addition to the information that can be obtained from normal joining (inner joining), outer joining provides a method of fetching the retrieval results. In outer joining, any inner table columns that do not meet a specified set of joining conditions are assigned null values. One use of outer joining is to join tables that have missing values.

Figure 2-38 shows an example of outer joining. In this example, a stock table and an order table are outer joined to retrieve the products with a stock quantity of less than 100; the retrieved information includes the product codes, product names, colors, and form numbers of products.

Figure 2-38 Example of outer joining

Figure 2-39 shows an example of outer joining with three or more tables. In this example, a stock table, an order table, and the previous month's order table are outer joined. For products that have a price of $50.00 or more, the product name, price, and this and the previous month's ordered quantity are retrieved.

Figure 2-39 Example of outer joining with three or more tables