示例：
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)
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |