限定模糊的列名

     在SQL连接中两张或者多张表中相同的列加前缀来限定模糊的列名，在各种子句中情况是不一样的，下面来举个例子。

有两张表分别是departments和employees,从这两张表可以看出有相同的两列（department_id,manager_id)

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)

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)

1.在natural  join 子句中，两个表相同的列在select子句中是不能加前缀的，非相同列无限制。

SQL> select department_id,e.manager_id from  employees e natural  join  departments;

select department_id,e.manager_id from  employees e natural  join  departments

                     *

ERROR at line 1:

ORA-25155: column used in NATURAL join cannot have qualifier

SQL> select department_id,employees.manager_id from  employees natural  join  departments;

select department_id,employees.manager_id from  employees natural  join  departments

                     *

ERROR at line 1:

ORA-25155: column used in NATURAL join cannot have qualifier

2.在Using子句中，两个表中相同的列在select子句中必须加前缀，但是若这个列在using子句中，那么在其它子句中就不能再加前缀了，非相同列无限制。

Manager_id是两表相同的列，并且在using子句中，所以不用加前缀，所以执行成功。

SQL> select employee_id,manager_id from  employees  join  departments  using(manager_id);

EMPLOYEE_ID MANAGER_ID

----------- ----------

        101        100

        102        100

        104        103

        

在select列表中有department_id列，是两表相同的列，并且不在using子句中所以必须加上前缀，如不加，则执行失败。

SQL> select employee_id,manager_id,department_id  from  employees e  join  departments  using(manager_id);

select employee_id,manager_id,department_id  from  employees e  join  departments  using(manager_id)

                              *

ERROR at line 1:

ORA-00918: column ambiguously defined

加了前缀执行成功。

SQL> select employee_id,manager_id,e.department_id  from  employees e  join  departments  using(manager_id);

EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID

----------- ---------- -------------

        201        100            20

        202        201            20

        206        205           110

        101        100            90

3.在On子句中，两个表相同的列在select子句中必须加上前缀，非相同列无限制。

相同列manager_id没有加前缀，执行失败。

SQL> select employee_id,manager_id from  employees e join  departments  d on (e.manager_id=d.manager_id); 

select employee_id,manager_id from  employees e join  departments  d on (e.manager_id=d.manager_id)

                   *

ERROR at line 1:

ORA-00918: column ambiguously defined

加前缀，执行成功。

SQL> select employee_id,e.manager_id from  employees e join  departments  d on (e.manager_id=d.manager_id); 

EMPLOYEE_ID MANAGER_ID

----------- ----------

        101        100

        102        100

        104        103

        105        103

        106        103

4.在oracle语法中，两个表的相同列在select子句中必须加上前缀，非相同列无限制。

相同列manager_id没有加前缀，执行失败。

SQL> select employee_id,manager_id,department_name  from employees,departments 

  2  where employees.manager_id=departments.manager_id;

select employee_id,manager_id,department_name  from employees,departments

                   *

ERROR at line 1:

ORA-00918: column ambiguously defined

加前缀，执行成功。

SQL> select employee_id, employees.manager_id,department_name  from employees,departments 

  2  where employees.manager_id=departments.manager_id;

EMPLOYEE_ID MANAGER_ID DEPARTMENT_NAME

----------- ---------- ------------------------------

        101        100 Executive

        102        100 Executive

        104        103 IT

        105        103 IT