On Wed, 09 Mar 2016 15:32:01 +0100
Jean-Christophe Deschamps <jcd at antichoc.net> wrote:
> > select id from a where id not in (select a.id from b);
> As I understand it, there is no more an a.id column in table b. It
> looks like SQLite is trying to get clever ignoring the "a." qualifier.
It's not ignoring the qualifier. It's processing an odd correlated
subquery. This would produce the same:
select id from a where id not in (select a.id);
For each row in a, select the id that is not the id in the row.
What might be a little suprising is that the columns named in the
SELECT need not come from the table in the FROM clause (if there is
one). For example, I'm sure you find this valid:
select id from a where id not in (
select 2 * (a.id / 2) from b
where a.id > b.id
);
Granted, that's a long walk for "odd ids in A greater than the smallest
id in B. But it's not SQL's job to force succinct expression.
--jkl
