示例:
SQL> select e.employee_id,d.department_name,department_id 2 from employees e join departments d using(department_id);
EMPLOYEE_ID DEPARTMENT_NAME DEPARTMENT_ID ----------- ------------------------------ ------------- 200 Administration 10 201 Marketing 20 202 Marketing 20 114 Purchasing 30 115 Purchasing 30 116 Purchasing 30 117 Purchasing 30 118 Purchasing 30 119 Purchasing 30 203 Human Resources 40 120 Shipping 50
EMPLOYEE_ID DEPARTMENT_NAME DEPARTMENT_ID ----------- ------------------------------ ------------- 121 Shipping 50 122 Shipping 50 123 Shipping 50
解析:
通过查看employees和departments两表的表结构可看出,这两张表有两个字段同名,现在我们只想关联一个同名字段,而并非让所有同名的字段自然关联,这时候就用到USING子句限定只要关联的字段。另外,关联的字段是两张表都有的,属于公共的,所以不能在该字段之前加表前缀。
SQL> desc employees; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
SQL> desc departments; Name Null? Type ----------------------------------------- -------- ---------------------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) |