LEFT JOIN and LEFT OUTER JOIN are the same animal. A left outer join must yield every row from the left input and (a) any matching rows from the right input and (b) nulls for any row that does not exist in the right input. To achieve this from the right input, the optimizer has chosen to use a full table scan. Why? I don't know. There is a "30% rule" that applies when deciding when to use an index, but there is no index involved here.
What happens (as reported by EXPLAIN) if you specify NO.OPTIMIZE for each query? > ----- Original Message ----- > From: "Anthony Youngman" <[EMAIL PROTECTED]> > To: "'u2-users@listserver.u2ug.org'" <u2-users@listserver.u2ug.org> > Subject: RE: [U2] Strange SQL query optimisation ... > Date: Fri, 18 Jul 2008 09:04:35 +0100 > > > Thanks. But I don't understand ... > > In the second query I've asked for a LEFT JOIN both times. What's > the difference between a LEFT JOIN and a LEFT OUTER JOIN? And why > when I ask it twice for LEFT JOINs does it do a left join once, and > a left outer join once? > > While I think I understand your comment about "a left outer join > must capture rows that may not be in the right table" I don't see > how any such rows could exist - the fact that we're matching @ID to > @ID guarantees that, surely? > > Unfortunately, I don't think creating an index is an option > > Sorry for troubling you for more detail, but my SQL isn't superb, > and I don't see why it should treat the two joins any differently - > they're both joining tables on a "key matches key" basis. > > Cheers, > Wol ------- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
