oracle 谓词是什么意思,oracle sql外连接的时候谓词的应用顺序是会影响结果的

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行,和非外连接的结果集相同.