php - Mysql returns no values when null values are present In the subquery -

i have 2 tables table1 , table2. , content follows

mysql> select id table1; +------+ | id   | +------+ |    1 | |    2 | |    3 | |    4 | +------+ 4 rows in set (0.00 sec)  mysql> select id table2; +------+ | id   | +------+ |  301 | |    2 | | null | +------+ 3 rows in set (0.00 sec) 

when hit below query in mysql console returns empty set

select id  table1  id  not in (select id table2); 

empty set (0.00 sec)

is there reason when there null values in sub query in , not in malfunction....?

i've solved using below query

select id  table1  id  not in (select id table2 id not null); +------+ | id   | +------+ |    1 | |    3 | |    4 | +------+ 

3 rows in set (0.00 sec)

just want know

thanks in advance :)

edit: this question tries clear air not enough

that how not in works. recommend use not exists instead:

select id  table1 t1 not exists (select 1 table2 t2 t1.id = t2.id); 

why not in work way? because of semantics of not in. remember null in sql (usually) means unknown value. hence, if have list of "(1, 2)" can "3" not in list. if have "(1, 2, unknown)" cannot that. instead, result null, treated false.

not exists not behave way, find more convenient use.