Of course they are not the same. One is an implicit equijoin and the other is an explicit outer join. Wondering if they are different is like windering if the colour of a car makes a difference in how fast it will go, then testing a yellow lambourgini against a purple volkswagon bug, and concluding that yellow cars are faster than purple cars.
> -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Paul Sanderson > Sent: Thursday, 11 June, 2015 04:46 > To: General Discussion of SQLite Database > Subject: [sqlite] implicit vs explicit joins > > I have just been asked whether implicit and explicit join are the same > and was given an example > > There queries below produce the same results but explain indicates > that they are different. > > My question though is in what instances are the actual results of such > a query likely to be different and is there a reason to use explicit > over implicit > > > sqlite> explain select text, handle.id from message,handle where > handle_id = handle.ROWID; > > 0|Init|0|14|0||00| > 1|OpenRead|0|5|0|6|00| > 2|OpenRead|1|12|0|2|00| > 3|Rewind|0|11|0||00| > 4|Column|0|5|1|0|00| > 5|MustBeInt|1|10|0||00| > 6|NotExists|1|10|1||00| > 7|Column|0|2|2||00| > 8|Column|1|1|3||00| > 9|ResultRow|2|2|0||00| > 10|Next|0|4|0||01| > 11|Close|0|0|0||00| > 12|Close|1|0|0||00| > 13|Halt|0|0|0||00| > 14|Transaction|0|0|44|0|01| > 15|TableLock|0|5|0|message|00| > 16|TableLock|0|12|0|handle|00| > 17|Goto|0|1|0||00| > > > > sqlite> explain SELECT message."text", handle.id FROM message LEFT > JOIN handle ON message.handle_id = handle."ROWID"; > > 0|Init|0|19|0||00| > 1|OpenRead|0|5|0|6|00| > 2|OpenRead|1|12|0|2|00| > 3|Rewind|0|16|0||00| > 4|Integer|0|1|0||00| > 5|Column|0|5|2|0|00| > 6|MustBeInt|2|12|0||00| > 7|NotExists|1|12|2||00| > 8|Integer|1|1|0||00| > 9|Column|0|2|3||00| > 10|Column|1|1|4||00| > 11|ResultRow|3|2|0||00| > 12|IfPos|1|15|0||00| > 13|NullRow|1|0|0||00| > 14|Goto|0|8|0||00| > 15|Next|0|4|0||01| > 16|Close|0|0|0||00| > 17|Close|1|0|0||00| > 18|Halt|0|0|0||00| > 19|Transaction|0|0|44|0|01| > 20|TableLock|0|5|0|message|00| > 21|TableLock|0|12|0|handle|00| > 22|Goto|0|1|0||00| > > > > > Paul > www.sandersonforensics.com > skype: r3scue193 > twitter: @sandersonforens > Tel +44 (0)1326 572786 > http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic- > Toolkit > -Forensic Toolkit for SQLite > email from a work address for a fully functional demo licence > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
