ASA SQL User's Guide
  Joins: Retrieving Data from Several Tables
    Inner and outer joins
      Outer joins

Outer joins and join conditions

A common mistake with outer joins is the placement of the join condition. In most cases, if you place restrictions on the null-supplying table in a WHERE clause, the join is equivalent to an inner join.

The reason for this is that most search conditions cannot evaluate to TRUE when any of their inputs are NULL. The WHERE clause restriction on the null-supplying table compares values to null, resulting in the elimination of the row from the result set. The rows in the preserved table are not preserved and so the join is an inner join.

The exception to this is comparisons that can evaluate to true when any of their inputs are NULL. These include IS NULL, IS UNKNOWN, IS FALSE, IS NOT TRUE, and expressions involving ISNULL or COALESCE.

Example 

For example, the following statement computes a left outer join.

SELECT *
FROM customer KEY LEFT OUTER JOIN sales_order
   ON sales_order.order_date < '2000-01-03'

In contrast, the following statement creates an inner join.

SELECT lname, order_date
FROM customer KEY LEFT OUTER JOIN sales_order
   WHERE sales_order.order_date < '2000-01-03'

The first of these two statements can be thought of as follows: First, left-outer join the customer table to the sales_order table. The result set includes every row in the customer table. For those customers who have no orders prior to January 3 2000, fill the sales order fields with nulls.

In the second statement, first left-outer join customer and sales_order. The result set includes every row in the customer table. For those customers who have no orders, fill the sales order fields with nulls. Next, apply the WHERE condition by selecting only those rows in which the customer has placed an order since January 3 2000. For those customers who have not placed orders, these values are NULL. Comparing any value to NULL evaluates to UNKNOWN. Hence, these rows are eliminated and the statement reduces to an inner join.

For more information about search conditions, see Search conditions.