On 10/7/2015 10:40 AM, Constantine Yannakopoulos wrote: > 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"
Can't reproduce. sqlite> create table item(itemcode integer); sqlite> insert into item(itemcode) values (1); sqlite> create table tmp_salesitm(itemid integer); sqlite> insert into tmp_salesitm(itemid) values (100); sqlite> select * from item where itemcode not in (select itemcode from tmp_salesitm); sqlite> select * from item where itemcode not in (select itemid from tmp_salesitm); 1 sqlite> select * from item where itemcode in (select itemcode from tmp_salesitm); 1 > So I wondered, shouldn't the first query throw an error No it should not. The query is valid, if meaningless. The correlated subquery is allowed to use columns from the enclosing query's tables. > instead of returning a result that does not make sense? It's not up to the engine to determine whether or not a query would make sense to a human. Its job is to check whether the query is syntactically valid, then execute it as written. > 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. That's what happens, yes. > 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? What, in your opinion, is the definition of "correlated subquery", if not "a subquery that happens to be using columns from the outer query"? > Shouldn't an > error be raised instead or am I missing something? You are 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 For me, it works the other way round (as I would expect). IN return all records, since it's essentially equivalent to select * from item where itemcode = itemcode; NOT IN returns no records, since it's essentially equivalent to select * from item where itemcode != itemcode; Are you sure you are not mixing up the two? -- Igor Tandetnik
