SELECT ID,ZONE,CODE FROM BIZ_SYS_NUMBERLIST
WHERE 1=1 AND ISUSERD=0 AND FLAG = 2 AND ZONE = '0574' AND
NOT exists (
SELECT 0 FROM BIZ_SYS_COMPAP_EXPAND where limit=ZONE||CODE and enabled=1 and FLAG='zjcode'
)有没更好的办法优化一下?
WHERE 1=1 AND ISUSERD=0 AND FLAG = 2 AND ZONE = '0574' AND
NOT exists (
SELECT 0 FROM BIZ_SYS_COMPAP_EXPAND where limit=ZONE||CODE and enabled=1 and FLAG='zjcode'
)有没更好的办法优化一下?
SELECT ID,ZONE,CODE FROM BIZ_SYS_NUMBERLIST
WHERE ZONE = '0574' AND
NOT exists (
SELECT 0 FROM BIZ_SYS_COMPAP_EXPAND where limit=ZONE||CODE and enabled=1 and FLAG='zjcode'
)
and id=1 AND ISUSERD=0 AND FLAG = 2;确定的需要放在where子句的最后
WHERE b.limit(+)=a.ZONE||a.CODE and b.enabled(+)=1 and b.FLAG(+)='zjcode'
and b.limit=null
AND 1=1 AND ISUSERD=0 AND FLAG = 2 AND ZONE = '0574'1、使用多个表的时候尽量给表指定别名,否则ORACLE需要从字段列表中检索以区分此字段是属于哪个表的
2、外连接比exists// not exists 效率高
and b.limit=null
本身连接 b.limit(+)=a.ZONE||a.CODE 查询是,当不存在
b.limit=a.ZONE||a.CODE 时 a表的值仍然输出到结果集,B表字段值此时为null
条件and b.limit=null正好把这一部分筛选出来