Hello SQLite team, I have two tables, "item" (items on sale) where a column named "itemcode" is the PK and "tmp_salesitm" (invoice item lines) where the foreign key column to "item" is called "itemid" and not "itemcode" for some reason. I wanted to find all records from the first with no matching records in the second (i.e. items that have never been sold). So I hastily typed:
select * from item where itemcode not in (select itemcode from tmp_salesitm) forgetting that the column name was different. To my amazement the query returned ALL rows from table "item" despite the fact that "tmp_salesitm" has several matching records. After investigation I located the name mismatch, corrected the query to: select * from item where itemcode not in (select itemid from tmp_salesitm) ran it and it returned the correct results. So I wondered, shouldn't the first query throw an error instead of returning a result that does not make sense? Then I thought that the SQL parser may have interpreted "itemcode" in the subquery as a reference to the "itemcode" column of the outer query. So I changed "itemcode" to "xxx" and I got an error I was expecting. My question is, is this behaviour normal? Should a NOT IN subquery, that uses a different from clause and is -to my knowledge- not correlated, be allowed to select columns of the outer query's FROM tables? Shouldn't an error be raised instead or am I missing something? FYI the outcome is similar if I replace "NOT IN" with "IN". With: select * from item where itemcode in (select itemcode from tmp_salesitm) I get zero records and with: select * from item where itemcode in (select itemid from tmp_salesitm) I get the correct matching records. Also, the "explain query plan" command shows a single full table scan on "item" for the first query and a more "reasonable" plan for the second query. I use SQLite version 3.8.11 in Windows 7. Thanks in advance and apologies if this has been asked before. -- Constantine
