0%

in和exists的取舍

in和exists的取舍

之前说过要小表驱动大表,即先遍历小表再遍历大表,接下来看一下in和exists的区别

in

1
2
3
4
5
select * from A where id in (select id from B)

等价于==
先遍历表B select id from B
再遍历表A select * from A where A.id = B.id

exists

1
2
3
4
5
select * from A where id exists (select 1 from A.id = B.id)

等价于
先遍历表A select * from A
再遍历表B select * from B where A.id = B.id

将主查询数据放到子查询中做验证,根据验证结果来确定主查询结果的去留

结论

根据执行顺序也就得知了什么时候该用in什么时候该用exists了

当表A数据集大于表B数据集时,使用in;当表B数据集大于表A数据集时,用exists