有两个表
table_A:
name1
name2table_B:
name,有索引table_B_idx第一段语句
select * from table_A a
where exists (select null from table_B where a.name1=b.name or a.name2=b.name);
第二段语句
select * from table_A a
where a.name1 in (select name from table_B) or a.name2 in (select name from table_B);
table_A:
name1
name2table_B:
name,有索引table_B_idx第一段语句
select * from table_A a
where exists (select null from table_B where a.name1=b.name or a.name2=b.name);
第二段语句
select * from table_A a
where a.name1 in (select name from table_B) or a.name2 in (select name from table_B);
一般内表小外表大用 in , 其它情况用 exists .实际还是要看执行计划和消耗。如果一切都有个万无一失的固定说法, 就没有DBA这个职业了。
-- table_B 字段name 只要有一个空值(null),整个查询就没有结果!
select * from table_A where name1 in(select name from table_B)-- table_B 没有字段name1,但是下面的执行不会出错!
select * from table_A where name1 in(select name1 from table_B)