Subject Re: Why would left outer join return FEWER records?
Author Svein Erling Tysvær
Make a habit of always qualifying all fields. Since you also select
STOREID, that field could come from either table, and having to check
all fields for no duplicates every time you make a small change is
more cumbersome than to add the A and B from the start. Suppose you
added prodbarcode as a new field to vendorproducts and then for a
completely different reason decided to recompile your program, then
your program could all of a sudden start to behave differently - even
though you didn't change your SQL at all. It would be almost 'disaster
as designed'.


--- In, Joe Martinez wrote:
> Oops, nevermind. I figured out the reason. The VENDORPRODUCTS table
> has a STOREID field as well, and so I guess the unqualified STOREID
> in the the "where" clause was causing it to throw out completely the
> rowswhere the "joined" STOREID from VENDORPRODUCTS was null. I
> changed the "where" clause to say A.storeid, and that fixed it.