Paul, I giggled when I saw that 🙂

To the original poster, the logic in the where clause is a bit strange but then we don't know your data 🙂

a.id1=b.id1

and a.lid1=b.lid2

and a.lid3=b.lid3

lid1 = lid2 sounds like it could have potential for an issue. However If you are joining non int or decimal types such as char and nchar, it is quite possible there are leading or trailing spaces on one table that match. For instance sql server wont match ' bob' = 'bob ' in that join type.

Also if you have 'massive' amounts of data, when you are using a left join, all records from the left table are returned and only matching records contain non null data. If only a few records match and you try to eyeball across a million rows, you may not see that non null data. Whereas the inner join you could be returning the 10 records only that actually do match which makes it seem like its only working on the Inner join.

Anyways my 2 cents to help out without data or data structures 😀