SQL> select department_name
from hr.departments dept
where department_id IN (select department_id from hr.employees emp);
2 3
DEPARTMENT_NAME
------------------------------
Administration
Marketing
Purchasing
Human Resources
Shipping
IT
Public Relations
Sales
Executive
Finance
Accounting
已选择11行。
SQL> select department_name
from hr.departments dept, hr.employees emp
where dept.department_id = emp.department_id; 2 3
DEPARTMENT_NAME
------------------------------
Administration
Marketing
Marketing
Purchasing
Purchasing
Purchasing
Purchasing
Purchasing
Purchasing
Human Resources
Shipping
DEPARTMENT_NAME
------------------------------
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
DEPARTMENT_NAME
------------------------------
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
DEPARTMENT_NAME
------------------------------
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
DEPARTMENT_NAME
------------------------------
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
Shipping
DEPARTMENT_NAME
------------------------------
IT
IT
IT
IT
IT
Public Relations
Sales
Sales
Sales
Sales
Sales
DEPARTMENT_NAME
------------------------------
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
DEPARTMENT_NAME
------------------------------
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Sales
DEPARTMENT_NAME
------------------------------
Sales
Sales
Sales
Sales
Sales
Sales
Sales
Executive
Executive
Executive
Finance
DEPARTMENT_NAME
------------------------------
Finance
Finance
Finance
Finance
Finance
Accounting
Accounting
已选择106行。
SQL> select distinct department_name
from hr.departments dept, hr.employees emp
where dept.department_id = emp.department_id;
2 3
DEPARTMENT_NAME
------------------------------
Administration
Accounting
Purchasing
Human Resources
IT
Public Relations
Executive
Shipping
Sales
Finance
Marketing
已选择11行。
IN半链接改成关联,就得去从
那从2个表返回数据呢？能改写成半链接吗？
SQL> select distinct dept.department_name,emp.department_id
from hr.departments dept, hr.employees emp
where dept.department_id = emp.department_id; 2 3
DEPARTMENT_NAME DEPARTMENT_ID
------------------------------ -------------
Marketing 20
Accounting 110
Human Resources 40
IT 60
Administration 10
Executive 90
Public Relations 70
Finance 100
Purchasing 30
Shipping 50
Sales 80
已选择11行。
SQL> select dept.department_name, emp.department_id
from hr.departments dept
where dept.department_id in
(select emp.department_id from hr.employees emp) 2 3 4 ;
select dept.department_name, emp.department_id
*
第 1 行出现错误:
ORA-00904: "EMP"."DEPARTMENT_ID": 标识符无效
从多个表返回数据的关联,无法改成半链接。