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

最新推荐文章于 2022-09-30 20:24:45 发布

原创最新推荐文章于 2022-09-30 20:24:45 发布 · 411 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql

数据库技术同时被 2 个专栏收录

33 篇文章

订阅专栏

SQL

24 篇文章

订阅专栏

本文通过实例对比了在SQL查询中使用不同联结条件（如employee_id=200作为联结条件与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
                                               Payroll

27 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
                                               Sales

27 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