oracel实际开发中,经常遇到这样的状况,两个表作外关联时,同时想对被关联表进行条件限制,容易出错,故在此总结下。
实例:
SQL1:
SELECT Count(*)
FROM econtract.cm_contracts_single_v smcon,
base.bas_regions_v reg ,
base.bas_regions_v Off
WHERE reg.region_id(+) = smcon.region
AND smcon.office = off.region_id(+)
AND reg.enable_flag(+) = 'Y'
AND off.enable_flag(+) = 'Y'
AND off.region_type(+) = 3
AND reg.region_type(+) = 2
;
SQL2:
SELECT Count(*)
FROM econtract.cm_contracts_single_v smcon,
base.bas_regions_v reg ,
base.bas_regions_v Off
WHERE reg.region_id(+) = smcon.region
AND smcon.office = off.region_id(+)
AND reg.enable_flag= 'Y'
AND off.enable_flag = 'Y'
AND off.region_type = 3
AND reg.region_type = 2
;
加粗部分为被外关联表的过滤条件。
二者的区别一看 就是过滤条件的写法不一样。
实质区别为:SQL1 不影响主表的查询总数:是对被关联表自身数据进行条件过滤.
SQL2 影响主表的查询总数:是对主表与外表作关联后的数据 进行条件过滤.
实例:
SQL1:
SELECT Count(*)
FROM econtract.cm_contracts_single_v smcon,
base.bas_regions_v reg ,
base.bas_regions_v Off
WHERE reg.region_id(+) = smcon.region
AND smcon.office = off.region_id(+)
AND reg.enable_flag(+) = 'Y'
AND off.enable_flag(+) = 'Y'
AND off.region_type(+) = 3
AND reg.region_type(+) = 2
;
SQL2:
SELECT Count(*)
FROM econtract.cm_contracts_single_v smcon,
base.bas_regions_v reg ,
base.bas_regions_v Off
WHERE reg.region_id(+) = smcon.region
AND smcon.office = off.region_id(+)
AND reg.enable_flag= 'Y'
AND off.enable_flag = 'Y'
AND off.region_type = 3
AND reg.region_type = 2
;
加粗部分为被外关联表的过滤条件。
二者的区别一看 就是过滤条件的写法不一样。
实质区别为:SQL1 不影响主表的查询总数:是对被关联表自身数据进行条件过滤.
SQL2 影响主表的查询总数:是对主表与外表作关联后的数据 进行条件过滤.