An INNER join (signified by the join operators "," (comma), JOIN or INNER JOIN) is a very different beast than an OUTER join (signified by the join operators LEFT JOIN or LEFT OUTER JOIN).
The first returns that subset of the cartesian product of the two tables where the join condition is met. Which may be empty. The second addtitionally returns, for each LHS row that is not already in the result set of the join, that LHS row joined to a row of all NULLs. And thus contains a least one row for each LHS row. If each and every record in your message table contains a valid rowid from the handle table, you will never notice the difference. Try SELECT * FROM message WHERE handle_id NOT IN (SELECT rowid FROM handle); it is probably empty. -----Urspr?ngliche Nachricht----- Von: Paul Sanderson [mailto:sandersonforensics at gmail.com] Gesendet: Donnerstag, 11. Juni 2015 12:46 An: General Discussion of SQLite Database Betreff: [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 ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
