外联结的ON条件与WHERE条件的区别

请注意以下联结的不同。employee_id = 200作为联结条件时，会有其余的行输出：HR@ prod> select /*+ ordered */ first_name , last_name , department_name   2  from employees e right outer join departments d on ( e.department_id = d.department_id and employee_id = 200 ) ;FIRST_NAME           LAST_NAME                 DEPARTMENT_NAME-------------------- ------------------------- ------------------------------Jennifer             Whalen                    Administration                                               Marketing                                               Purchasing                                               Human Resources                                               Shipping                                               IT                                               Public Relations                                               Sales                                               Executive                                               Finance                                               Accounting                                               Treasury                                               Corporate Tax                                               Control And Credit                                               Shareholder Services                                               Benefits                                               Manufacturing                                               Construction                                               Contracting                                               Operations                                               IT Support                                               NOC                                               IT Helpdesk                                               Government Sales                                               Retail Sales                                               Recruiting                                               Payroll27 rows selected.employee_id = 200作为where条件时，会对连接的结果集做筛选。HR@ prod> select /*+ ordered */ first_name , last_name , department_name   2  from employees e right outer join departments d on ( e.department_id = d.department_id )   3  where employee_id = 200 ;FIRST_NAME           LAST_NAME                 DEPARTMENT_NAME-------------------- ------------------------- ------------------------------Jennifer             Whalen                    Administration使用department_id作为筛选条件时也是一样的HR@ prod> select /*+ ordered */ first_name , last_name , department_name   2  from employees e right outer join departments d on ( e.department_id = d.department_id and d.department_id = 10 ) ;FIRST_NAME           LAST_NAME                 DEPARTMENT_NAME-------------------- ------------------------- ------------------------------Jennifer             Whalen                    Administration                                               IT Helpdesk                                               Retail Sales                                               Shipping                                               Manufacturing                                               Payroll                                               Operations                                               Recruiting                                               IT                                               Human Resources                                               Contracting                                               Finance                                               Benefits                                               Executive                                               Treasury                                               Government Sales                                               Marketing                                               NOC                                               Construction                                               Public Relations                                               Corporate Tax                                               Control And Credit                                               IT Support                                               Accounting                                               Purchasing                                               Shareholder Services                                               Sales27 rows selected.HR@ prod> HR@ prod> HR@ prod> select /*+ ordered */ first_name , last_name , department_name   2  from employees e right outer join departments d on ( e.department_id = d.department_id ) where d.department_id = 10    3  ;FIRST_NAME           LAST_NAME                 DEPARTMENT_NAME-------------------- ------------------------- ------------------------------Jennifer             Whalen                    Administration