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');
sqlite> insert into home values ('path2');
sqlite> insert into work values ('path2');
sqlite> insert into work values ('path3');
sqlite> 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
