Oh, jeeze. Wow. And I know all that... I cleverly managed to ignore the "WHERE" and think it was an AND because it was one the same line.
Duh. Thanks. -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Reinhard Max Sent: Monday, July 27, 2015 12:04 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines On Mon, 27 Jul 2015 at 17:48, Marc L. Allen wrote: > When would that specific LEFT JOIN ever do anything except return > NULLs for the right table? It only accepts rows from work where fpath > is null, and only joins those rows where fpath = home.fpath. > Since fpath must be null, home.fpath should never be equal. You're assuming that "LEFT JOIN ... ON ..." behaves the same as "JOIN ... WHERE ...", which is not the case as it would defeat the purpose of a left join. sqlite> create table home (fpath text); sqlite> create table work (fpath text); sqlite> insert into home values ('path1'); insert into home values sqlite> ('path2'); insert into work values ('path2'); insert into work sqlite> values ('path3'); SELECT home.fpath ...> FROM home ...> LEFT JOIN work ON work.fpath = home.fpath ...> WHERE work.fpath IS NULL; path1 See, it returns the rows of home.fpath that don't have a match in work.fpath. cu Reinhard _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original.
