Left Outer Join - SQL2005

Question

I thought I knew enough SQL, but I am having problem with a left outer join.

I have an expense detail record that needs to link to a table by dept and account_code.

The query looks something like this:

SELECT Detail.Spend, Budget.BudgetAmt 
FROM detail left outer join budget 
    ON detail.dept = budget.dept 
    AND dept.account_code = budget.account_code

This works great as long as there is a record that exactly matches the join conditions. But sometimes, there is no matching budget item. I want to get back the Detail.Spend from the details table with nulls for the budgetAmt. Instead, I don't get this record at all.

Isn't Left Outer Join supposed to return the left (detail) table when there is no match? Is there something different when multiple criteria are used as I do here?

Thanks

Answer 1

The LEFT JOIN operator should behave as you expect. Is your example the entire query? I ask because sometimes I forget that putting a condition on the joined table in the WHERE clause is likely to cause this problem unless I account for NULL values.