left outer join not working as expected

[t1950]

this select doesn't return what i expect

select oh.customer_id, oh.order_number, oh.num_of_line_items, isNull( max( oli.line_number ), 0 ), oh.order_date  
from order_header oh left outer join order_line_items oli on oh.order_seq_num = oli.order_seq_num 
and oh.order_date >= '2013-10-01'   
group by oh.customer_id, oh.order_number, oh.num_of_line_items , oh.order_date

it returns orders dated before 2013-10-01

this select returns what i expect

select oh.customer_id, oh.order_number, oh.num_of_line_items, isNull( max( oli.line_number ), 0 ), oh.order_date  
from order_header oh left outer join order_line_items oli on oh.order_seq_num = oli.order_seq_num   
where oh.order_date >= '2013-10-01'  
group by oh.customer_id, oh.order_number, oh.num_of_line_items , oh.order_date

note the order date qualifier is in the WHERE clause

i thought i understood left outer joins, but now i'm confused

[VolkerBarth · Accepted solution]

That's expected behaviour:

The first query will return all rows from table order_header (independent of the order_date) and join those of them with an according order_date >= 2013-10-01 to the according order_line_items. So order headers before that date will show up without their items.

I guess the misunderstanding has to do with the following:

If you want to filter on the null-supplying side (here on order_line_items, say to restrict them to the first n items per order header or the like), then you have to specify that condition in the join's ON clause - otherwise (i.e. in the WHERE clause) you usually will turn the left join into an inner join (a common mistake, cf. Breck's list of characteristic errors no. 1).

But here you seem to want to filter on the preserved side, and then you can simply use the WHERE clause as usual.

(A further method to do so would be to use a derived table for order_header with the according WHERE clause and left join that derived table to the order_line_items table.)

[VolkerBarth · Accepted solution]

FWIW: The "null-supplying side" problem is discussed here in this FAQ with a somewhat very similar title (and OP):

• left outer join not working as expected

Note: This link is not a self-join self-link:)