When joining more than 2 tables, with a shared column name, the "using" join constraint does not apply to the tables strictly to the left and right of the join operator. The documentation is in https://www.sqlite.org/lang_select.html:

When more than two tables are joined together as part of a FROM clause, the join operations are processed in order from left to right. In other words, the FROM clause (A join-op-1 B join-op-2 C) is computed as ((A join-op-1 B) join-op-2 C).

If there is a USING clause then each of the column names specified must exist in the datasets to both the left and right of the join-operator. For each pair of named columns, the expression "lhs.X = rhs.X" is evaluated for each row of the cartesian product as a boolean expression.

If there is a USING clause then each of the column names specified must exist in the datasets to both the left and right of the join-operator.

In the best case, a join b join c using (a) should use a from b if it exists there. In the worst case, if tables a and b both have an 'a', then the using (a) should find a to be ambiguous (after a join b, the data set contains a.a and b.a).

Here is a reproduction (sqlite3 < repro.sql):

create table a(a);
create table b(a);
create table c(a);
insert into a values (1), (2);
insert into b values (2), (3);
insert into c values (3), (4);
.mode table
.echo on
-- this should join b.a and c.a giving us
-- 1 3
-- 2 3
select * from a join b join c using (a);
-- if we do it manually
select * from a join b join c on b.a=c.a;
-- (without b.a and c.a being combined into one column, which is expected).

The output from the last statement:

+---+---+---+
| a | a | a |
+---+---+---+
| 1 | 3 | 3 |
| 2 | 3 | 3 |
+---+---+---+

Please also see my previous issues:

• https://sqlite.org/forum/info/4400a8d215ae8ae4
• https://sqlite.org/forum/info/600f887776ebe0c8

a join b join c using (a) should use a from b if it exists there.
In the worst case, if tables a and b both have an a, then the using (a) should find a to be ambiguous

But if the a and b tables were joined using the a column too, as in your example, then "a"."a" and "b"."a" are by definition equivalent, so the ambiguity could be lifted.

I'm not saying that's what's happening, just thinking logically through your example (if I understood it right).

Yes I think you're correct here. But I'm not joining table a and b on a column.

I suspect this is what's happening under the hood, I guess it's not clear that b's column a is being renamed. This does explain the behaviour, thanks.

Per the quoted sections in my OP, it's not clear that b's column a would not be favored over a's. It's quite unexpected. I'm not sure that much can come of this, your explanation above makes a lot of sense, perhaps this can be highlighted in the documentation though.