select * from tb t where exists(select 1 from tb where id = t.id and id in('1','2'))
create table tb ( id int, name varchar(10) ) insert into tb values(1,'a') insert into tb values(2,'b') insert into tb values(3,'c') insert into tb values(4,'d') select * from tb t where exists(select 1 from tb where id = t.id and id in(1,2))id name ----------- ---------- 1 a 2 b
select * from table t where exists(select id from table where id=t.id); --lz测试下。
--in和existsselect * from 大表 where cc in (小表) select * from 小表 where exists(大表)表A(小表),表B(大表)1:select * from A where cc in (select cc from B) 效率低,用到了A表上cc列的索引; select * from A where exists(select cc from B where cc=A.cc) 效率高,用到了B表上cc列的索引。
exists(select 1 from tb where id = t.id and id in('1','2'))
(
id int,
name varchar(10)
)
insert into tb values(1,'a')
insert into tb values(2,'b')
insert into tb values(3,'c')
insert into tb values(4,'d')
select * from tb t
where exists(select 1 from tb where id = t.id and id in(1,2))id name
----------- ----------
1 a
2 b
--lz测试下。
--in和existsselect * from 大表 where cc in (小表)
select * from 小表 where exists(大表)表A(小表),表B(大表)1:select * from A where cc in (select cc from B)
效率低,用到了A表上cc列的索引;
select * from A where exists(select cc from B where cc=A.cc)
效率高,用到了B表上cc列的索引。