查询为空 select * from core_custbaseinfo t where t.id_num is NULL
查询非空 select * from core_custbaseinfo t where t.id_num is not NULL
查询存在 select * from core_orginfo t where exists (select sysdate from core_custbaseinfo d where d.org_id = t.org_id)
查询不存在 select * from core_orginfo t where not exists (select sysdate from core_custbaseinfo d where d.org_id = t.org_id)
查询in select * from core_orginfo t where t.org_id in (select d.org_id from core_custbaseinfo d )
查询not in select * from core_orginfo t where t.org_id not in (select d.org_id from core_custbaseinfo d )
not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;
而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大,用not exists都比not in要快。
也就是说,in和exists需要具体情况具体分析,not in和not exists就不用分析了,尽量用not exists就好了。