Wrong SELECT subquery in 'IN' condition

Question

I made a query like this

SELECT *
FROM   TABLE_A
WHERE  1=1
   AND ID_NO IN (
         SELECT ID_NO
         FROM   TABLE_B
         WHERE  SEQ = '1'
   )

The problem was there is no column 'ID_NO' in TABLE_B.So I was expecting that query doesn't work. But This query worked. I don't understand why.

Why didn't it cause error?

Answer 1

The query is correct if table_B does not have a column named ID_NO, but table_A has. Then you would have a correlated subquery, where the subquery select ID_NO refers to the outer ID_NO-attribute of table_A (persumably makes no sense, but is correct for the compiler).

Consider the following schema:

create table table_a (
  id_no int
  );

create table table_b (
    other_id_no int
    );

insert into table_a values (1),(2);
insert into table_b values (1),(3);

Then, the following query will compile; but it will always yield an empty result, because it actually means something like where id_no not in (id_no):

select * from  table_a where id_no not in (select id_no from table_b);

When dealing with subqueries, I'd suggest to use table aliases in order to avoid such unintended behaviour. For example, the following query does not compile, and the compiler gives you the hint what is wrong:

select * from  table_a a where a.id_no not in (select b.id_no from table_b b);
Error: Unknown column 'b.id_no' in 'field list'

Correcting the error then leads to:

select * from  table_a a where a.id_no not in (select b.other_id_no from table_b b);