关于USING

5dm

示例：

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)