in和exist用法

## name 
id    name       class_id
1     xiaoming   1
null  liming     2
3     xiaozhang  3
## class
id    class_name
1     class_1
2     class_2
null  class_3

insert into table name values(1,'xiaoming',1);
insert into table name values(null,'liming',2);
insert into table name values(3,'xiaozhang',3);   

insert into table `class` values(1,'class_1');
insert into table `class` values(2,'class_2');
insert into table `class` values(null,'class_3');  

in

select id,name,class_id from tmp.name where id in (select id from tmp.`class`)
结果：
1 xiaoming 1

select id,name,class_id from tmp.name where not id in (select id from tmp.`class`)
结果：
无值

exists

select id,name,class_id from tmp.name where exists (select id from tmp.`class` where name.id = `class`.id)
结果：
1    xiaoming 1

select id,name,class_id from tmp.name where not exists (select id from tmp.`class` where name.id = `class`.id)
结果：
null  liming     2
3     xiaozhang  3

总结

1. 对于not in语法要注意null问题，需要给过滤掉，否则查询出来后，结果都无值
2. exists语法根据实际情况使用，可参考left semi join