ASA SQL User's Guide
Joins: Retrieving Data from Several Tables
Inner and outer joins
Outer joins
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.
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.