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.
> 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.
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? ;-)
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
