create table t (
id Varchar2(10) primary key,
dmid Varchar2(10),
sbh Varchar2(10)
)
crate table dm(
dmid Varchar2(10),
dmName Varchar2(10),
sbh Varchar2(10)
)select * from t where dmid in ('01','02','03') and t.sbh ='12345'
select * from t where sbh ='12345' and exists(
select * from dm where t.dmid = dm.dmid
)select * from t where dmid in('01','02','03') and exists(
select * from dm where t.dmid = dm.dmid and sbh ='12345'
)select * from t where exists(
select * from dm where t.dmid = dm.dmid and sbh ='12345' and dmid in('01','02','03')
)select * from t where dmid t.sbh ='12345' and dmid='01'
union
select * from t where dmid t.sbh ='12345' and dmid='02'
union
select * from t where dmid t.sbh ='12345' and dmid='03'如果t表和dm表都很大,那种写法效率高啊?高人看下嘛
id Varchar2(10) primary key,
dmid Varchar2(10),
sbh Varchar2(10)
)
crate table dm(
dmid Varchar2(10),
dmName Varchar2(10),
sbh Varchar2(10)
)select * from t where dmid in ('01','02','03') and t.sbh ='12345'
select * from t where sbh ='12345' and exists(
select * from dm where t.dmid = dm.dmid
)select * from t where dmid in('01','02','03') and exists(
select * from dm where t.dmid = dm.dmid and sbh ='12345'
)select * from t where exists(
select * from dm where t.dmid = dm.dmid and sbh ='12345' and dmid in('01','02','03')
)select * from t where dmid t.sbh ='12345' and dmid='01'
union
select * from t where dmid t.sbh ='12345' and dmid='02'
union
select * from t where dmid t.sbh ='12345' and dmid='03'如果t表和dm表都很大,那种写法效率高啊?高人看下嘛
相对来说,exists一般比in快一些,具体还要看sql的执行计划,谁的执行计划好,谁就是好sql
select /*+ use_hash(t,dm)*/
*
from t,dm
where t.dmid=dm.dmid
and dm.sbh='12345'
如果标很大的话,就加上提示
/*+ use_hash(t,dm)*/如果不是很大就不用了。