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.

The following figure shows, as an example of an outer join, the procedure for performing an outer join on the stock table and order table to retrieve product codes, produce names, colors, and form numbers of those products whose stock quantity is less than 100.

Figure 2-38 Example of outer joining

The following figure shows an example of an outer join with three or more tables. This example performs an outer join on the stock table, the current month's order table, and the previous month's order table to retrieve the product names and unit prices, as well as the current and previous months' order quantities for all products whose price is $50.00 or more.

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