SQL CODE

1.查询 EXISTS -- NOT EXISTS -- IN -- NOT IN

1.1 很多时候用 exists 代替 in 是一个好的选择

   select num from a where num in(select num from b)

   用下面的语句替换：

   select num from a where exists(select 1 from b where num=a.num)

 1.2 in 和 not in 也要慎用，否则会导致全表扫描

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

Select id from t where num between 1 and 3

1.3 NOT EXISTS示例

表[BaseDT]有170W行数据，
表[AktNumber、BktNumber、CktNumber、DktNumber、EktNumber  分别约有68W——160W行数据
同时满足5个条件的查询（非AktNumber、BktNumber、CktNumber、DktNumber、EktNumber的记录）有以下2种方法

---代码1（方法1）
select id,notext from [BaseDT] 
where 1=1
and not exists(select 1 from AktNumber where personid=[BaseDT].id )
and not exists(select 1 from BktNumber where personid=[BaseDT].id )
and not exists(select 1 from CktNumber where personid=[BaseDT].id )
and not exists(select 1 from DktNumber where personid=[BaseDT].id )
and not exists(select 1 from EktNumber where personid=[BaseDT].id )
 
---代码2（方法1）这种方法查询速度快，如果仅仅只有这5个条件的查询，LZ比较倾向这种 （实际上查询条件有22种，包括其他字段的查询）
select a.id,a.notext from BaseDT a left join AktNumber b on a.id=b.personid where b.personid is null
union  all  select a.id,a.notext from BaseDT a left join BktNumber b on a.id=b.personid where b.personid is null
union  all  select a.id,a.notext from BaseDT a left join CktNumber b on a.id=b.personid where b.personid is null
union  all  select a.id,a.notext from BaseDT a left join DktNumber b on a.id=b.personid where b.personid is null
union  all  select a.id,a.notext from BaseDT a left join EktNumber b on a.id=b.personid where b.personid is null