限定模糊的列名
在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