半链接和关联转换

SQL> select  department_namefrom hr.departments deptwhere department_id IN (select department_id from hr.employees emp);  2    3  DEPARTMENT_NAME------------------------------AdministrationMarketingPurchasingHuman ResourcesShippingITPublic RelationsSalesExecutiveFinanceAccounting已选择11行。SQL> select   department_name from hr.departments dept, hr.employees emp where dept.department_id = emp.department_id;  2    3  DEPARTMENT_NAME------------------------------AdministrationMarketingMarketingPurchasingPurchasingPurchasingPurchasingPurchasingPurchasingHuman ResourcesShippingDEPARTMENT_NAME------------------------------ShippingShippingShippingShippingShippingShippingShippingShippingShippingShippingShippingDEPARTMENT_NAME------------------------------ShippingShippingShippingShippingShippingShippingShippingShippingShippingShippingShippingDEPARTMENT_NAME------------------------------ShippingShippingShippingShippingShippingShippingShippingShippingShippingShippingShippingDEPARTMENT_NAME------------------------------ShippingShippingShippingShippingShippingShippingShippingShippingShippingShippingShippingDEPARTMENT_NAME------------------------------ITITITITITPublic RelationsSalesSalesSalesSalesSalesDEPARTMENT_NAME------------------------------SalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesDEPARTMENT_NAME------------------------------SalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesDEPARTMENT_NAME------------------------------SalesSalesSalesSalesSalesSalesSalesExecutiveExecutiveExecutiveFinanceDEPARTMENT_NAME------------------------------FinanceFinanceFinanceFinanceFinanceAccountingAccounting已选择106行。SQL> select  distinct department_name from hr.departments dept, hr.employees emp where dept.department_id = emp.department_id;  2    3  DEPARTMENT_NAME------------------------------AdministrationAccountingPurchasingHuman ResourcesITPublic RelationsExecutiveShippingSalesFinanceMarketing已选择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  20Accounting 110Human Resources   40IT  60Administration  10Executive  90Public Relations  70Finance  100Purchasing  30Shipping  50Sales  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": 标识符无效从多个表返回数据的关联,无法改成半链接。