try-- select count(1) from tab1 where exists(select 1 from tab2 where id=tb1.id and name=tb1.name)
写错一个表名,修正: select count(1) from tab1 where exists(select 1 from tab2 where id=tab1.id and name=tab1.name)
select * from table1 a where exists(select 1 from table2 where a.id= id and a.name=name)select * from table1 a left join table2 b on a.id=b.id and a.name = b.name where a.id is not null
select count(*) from tb2,tb1 where tb2.id=tb1.id and tb2.name=tb2.name
select count(1) from tab1 a ,tab2 b where a.id = b.id and a.name = b.name
大家有没有考虑到TAB1和TAB2数据量的问题啊?TAB1数据量是TAB2数据量的100倍啊?
和我想的一样,据说exists可以提高效率!
这个与数据的重复度,可能的匹配次数都有关 光给个量很难说怎么最优select count(*) from table2 a left join table1 b on a.id=b.id and a.name = b.name where b.id is not null
select
count(1)
from
tab1
where
exists(select 1 from tab2 where id=tb1.id and name=tb1.name)
select
count(1)
from
tab1
where
exists(select 1 from tab2 where id=tab1.id and name=tab1.name)
from table1 a
left join table2 b on a.id=b.id and a.name = b.name
where a.id is not null
select count(*) from tb2,tb1 where tb2.id=tb1.id and tb2.name=tb2.name
和我想的一样,据说exists可以提高效率!
光给个量很难说怎么最优select count(*)
from table2 a
left join table1 b on a.id=b.id and a.name = b.name
where b.id is not null