What is the precedence of multiple JOIN statements in sqlite?

Question

The following two queries are returning different results, to my surprise:

SELECT *
  FROM foo
       JOIN bar
         ON bar.id=foo.bar_id

       JOIN baz
         ON baz.id=foo.baz_id

       LEFT JOIN zig
         ON zig.foo_id=foo.id;

and:

SELECT *
  FROM foo
       LEFT JOIN zig
         ON zig.foo_id=foo.id

       JOIN bar
         ON bar.id=foo.bar_id

       JOIN baz
         ON baz.id=foo.baz_id;

I'm imagining that it shouldn't matter when you LEFT JOIN on zig, since I'm not using zig's columns when joining the bar and baz. However, it appears that in the latter, the JOIN-ing of bar and baz swallows up the rows where zig had null values... why is that?

Answer 1

These are different because of where your left join is in the statement. Remember that joins are not just on the two tables you are comparing fields on, but rather everything you have joined up to that point and the next table.

So in the first statement you are filtering out rows from foo in your first join (those that do not have bar_id = bar.id)

but in the second statement you are including all of foo with your left join.

This was harder to articulate than I anticipated when I started to write this, let me know if it makes sense to you.

Answer 2

This works fine. I was wrong.