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
