oracle sql外连接的时候谓词的应用顺序是会影响结果的, 非外连接则没有关系, 不过oracle之所以这样,虽然理解上觉得有点不可思议, 应该也是有其原因的.
SELECT COUNT(DISTINCT B.COMPANY_ID)
FROM (SELECT *
FROM ODS.BAS_TAB_EMPLOYEE_FJYD C
WHERE C.END_DATE = TO_DATE('3000-12-31', 'YYYY-MM-DD')
AND C.REG_DATE < TO_DATE('2010-01-24 00:00:00' /*:v_end_dt*/,
'YYYY-MM-DD HH24:MI:SS')
AND (C.EMPLOYEE_STATUS = 1 OR
C.EMPLOYEE_STATUS = 0 AND
C.PAUSE_DATE >=
TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS') OR
NVL(C.PAUSE_DATE, C.UNREG_DATE) >=
TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS'))) C,
ODS.BAS_TAB_COMPANY_FJYD B
WHERE B.COMPANY_ID = C.COMPANY_ID
AND B.END_DATE = TO_DATE('3000-12-31', 'YYYY-MM-DD')
AND B.REG_DATE <
TO_DATE('2010-01-24 00:00:00' /*:v_end_dt*/, 'YYYY-MM-DD HH24:MI:SS')
AND (B.COMPANY_STATUS = 1 OR
B.COMPANY_STATUS = 0 AND
B.PAUSE_DATE >= TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS') OR
NVL(B.PAUSE_DATE, B.UNREG_DATE) >=
TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS'))
---4813行
SELECT COUNT(DISTINCT B.COMPANY_ID)
FROM ODS.BAS_TAB_COMPANY_FJYD B, ODS.BAS_TAB_EMPLOYEE_FJYD C
WHERE C.COMPANY_ID = B.COMPANY_ID
AND B.END_DATE = TO_DATE('3000-12-31', 'YYYY-MM-DD')
AND B.REG_DATE <
TO_DATE('2010-01-24 00:00:00' /*:v_end_dt*/, 'YYYY-MM-DD HH24:MI:SS')
AND (B.COMPANY_STATUS = 1 OR
B.COMPANY_STATUS = 0 AND
B.PAUSE_DATE >= TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS') OR
NVL(B.PAUSE_DATE, B.UNREG_DATE) >=
TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS'))
AND C.END_DATE = TO_DATE('3000-12-31', 'YYYY-MM-DD')
AND C.REG_DATE <
TO_DATE('2010-01-24 00:00:00' /*:v_end_dt*/, 'YYYY-MM-DD HH24:MI:SS')
AND (C.EMPLOYEE_STATUS = 1 OR
C.EMPLOYEE_STATUS = 0 AND
C.PAUSE_DATE >= TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS') OR
NVL(C.PAUSE_DATE, C.UNREG_DATE) >=
TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS'));
--4813行.
SELECT COUNT(DISTINCT B.COMPANY_ID)
FROM (SELECT *
FROM ODS.BAS_TAB_EMPLOYEE_FJYD C
WHERE C.END_DATE = TO_DATE('3000-12-31', 'YYYY-MM-DD')
AND C.REG_DATE < TO_DATE('2010-01-24 00:00:00' /*:v_end_dt*/,
'YYYY-MM-DD HH24:MI:SS')
AND (C.EMPLOYEE_STATUS = 1 OR
C.EMPLOYEE_STATUS = 0 AND
C.PAUSE_DATE >=
TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS') OR
NVL(C.PAUSE_DATE, C.UNREG_DATE) >=
TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS'))) C,
ODS.BAS_TAB_COMPANY_FJYD B
WHERE B.COMPANY_ID = C.COMPANY_ID(+)
AND B.END_DATE = TO_DATE('3000-12-31', 'YYYY-MM-DD')
AND B.REG_DATE <
TO_DATE('2010-01-24 00:00:00' /*:v_end_dt*/, 'YYYY-MM-DD HH24:MI:SS')
AND (B.COMPANY_STATUS = 1 OR
B.COMPANY_STATUS = 0 AND
B.PAUSE_DATE >= TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS') OR
NVL(B.PAUSE_DATE, B.UNREG_DATE) >=
TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS'))
--8441行
SELECT COUNT(DISTINCT B.COMPANY_ID)
FROM ODS.BAS_TAB_COMPANY_FJYD B, ODS.BAS_TAB_EMPLOYEE_FJYD C
WHERE C.COMPANY_ID(+) = B.COMPANY_ID
AND B.END_DATE = TO_DATE('3000-12-31', 'YYYY-MM-DD')
AND B.REG_DATE <
TO_DATE('2010-01-24 00:00:00' /*:v_end_dt*/, 'YYYY-MM-DD HH24:MI:SS')
AND (B.COMPANY_STATUS = 1 OR
B.COMPANY_STATUS = 0 AND
B.PAUSE_DATE >= TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS') OR
NVL(B.PAUSE_DATE, B.UNREG_DATE) >=
TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS'))
AND C.END_DATE = TO_DATE('3000-12-31', 'YYYY-MM-DD')
AND C.REG_DATE <
TO_DATE('2010-01-24 00:00:00' /*:v_end_dt*/, 'YYYY-MM-DD HH24:MI:SS')
AND (C.EMPLOYEE_STATUS = 1 OR
C.EMPLOYEE_STATUS = 0 AND
C.PAUSE_DATE >= TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS') OR
NVL(C.PAUSE_DATE, C.UNREG_DATE) >=
TO_DATE('2010-01-23 00:00:00' /*:v_start_dt*/,
'YYYY-MM-DD HH24:MI:SS'));
--4813行,和非外连接的结果集相同.
