On Wednesday, 5 November, 2014 22:23, James Lowden said: >On Wed, 05 Nov 2014 08:24:47 -0700, "Keith Medcalf" <kmedc...@dessus.com> >wrote:
>> The two queries are different. They may end up with the same result, >> but you are asking different questions. In the first you are >> returning only matching rows. In the later you are requesting a >> projection (outer join) then applying the conditional (where) to the >> projection. >I'm surprised to see you say that, Keith, because it's not true. >An SQL query is one piece, integral and whole. There's no >before & after, no first this then that. Projection and selection are >independent operations and can be applied in either order, at the >discretion of the implementation. The only requirement, as you know, is >that the criteria be met. You are, or course, entirely correct. Premature optimization based on known implementation details is hard to avoid though. >> An index on profiles.userid will speed it up, but you are still >> asking a different question. You cannot change the table visitation >> order around a left outer join because it changes the question. >Actually, he can't change the visitation order because he doesn't >*control* the visitation order. That's up to SQLite. He can only ask a >question. Again, that is true. >Equivalent to an outer join is a union. Let's look at it that way >(simplified slightly for clarity): >>SELECT * FROM bugs b LEFT JOIN profiles p ON p.userid=b.assigned_to >>WHERE p.login_name='vita...@yourcmc.ru' > > select b.*, p.login_name > from bugs as b join profiles as p > on p.userid=b.assigned_to > where login_name='vita...@yourcmc.ru' > UNION > select *, NULL > from bugs > where assigned_to not in (select userid from profiles) > and NULL = 'vita...@yourcmc.ru' > >Same question, differently expressed. How much work would you suggest >the system do to answer the second part of that query? ;-) Hehehe. None because it cannot possibly have any results. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
