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