[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] SQLite vs. Oracle (parallelized)
From:       "Igor Tandetnik" <itandetnik () mvps ! org>
Date:       2009-02-27 14:20:21
Message-ID: go8sr9$i1t$1 () ger ! gmane ! org
[Download RAW message or body]

"Chris Wedgwood" <cw@f00f.org> wrote in message
news:20090226172406.GA685769@puku.stupidest.org
> On Thu, Feb 26, 2009 at 09:53:18AM -0600, Jay A. Kreibich wrote:
>
>>   I've always wondered about this... someone please correct me if I'm
>>   wrong, but my understanding is that there wasn't any difference
>>   between a left and right join except for the argument order.  It
>>   seems like implementing right joins should be pretty easy by just
>>   having the parser reverse the arguments when it builds the parse
>>   tree.  I realize that might screw with the optimizer a bit, but is
>>   there anything obvious I'm missing?
>
> When you use a right and a left join together you can't do that.
> Sometimes you can restructure the queries to make use or two left
> joins or a sub-query, but it's often cumbersome and not always the
> same thing.
>
> If in general there is a way to turn something like
>
>  select c1,c2,c3 from m left join l on l.mid=m.id right join r on
> l.rid=r.id
>
> into something conceptually the same that only uses left joins then
> perhaps the optimizer could do this for us.

An equivalent query using only left joins would look something like 
this:

select c1,c2,c3
from r left join (m left join l on l.mid=m.id) ml on ml.rid=r.id;

The problem, I believe, is not in parsing right joins or replacing them 
with equivalent left joins, but in optimizing the resulting statement. 
SQLite's optimizer is fairly simple: I suspect if it sees something like 
the above, it'll throw its hands up in the air and resort to full table 
scans all around.

For this reason, right joins likely have to be natively supported by the 
optimizer - you can't just fake them by transforming the parse tree.

Igor Tandetnik 



_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[prev in list] [next in list] [prev in thread] [next in thread]