SQL> select deptno,emp.empno,ename,job,loc from emp join dept using(deptno);(不能指明deptno属于哪个列)
DEPTNO EMPNO ENAME JOB LOC
------ ----- ---------- --------- -------------
10 111 aa NEW YORK
10 7934 MILLER CLERK NEW YORK
10 7839 KING PRESIDENT NEW YORK
10 7782 CLARK MANAGER NEW YORK
20 7566 JONES MANAGER DALLAS
20 7369 SMITH CLERK DALLAS
20 7902 FORD ANALYST DALLAS
20 7876 ADAMS CLERK DALLAS
20 7788 SCOTT ANALYST DALLAS
30 7499 ALLEN SALESMAN CHICAGO
30 7844 TURNER SALESMAN CHICAGO
30 7900 JAMES CLERK CHICAGO
30 7521 WARD SALESMAN CHICAGO
30 7698 BLAKE MANAGER CHICAGO
30 7654 MARTIN SALESMAN CHICAGO
15 rows selected
两个表join在使用using时,共同的列deptno,在select列表中不能标明deptno具体属于哪个表,
而且using中能使用一个列,
nature join和using语句互斥,不能同时使用。
但是在这个语句中必须标明deptno属于哪个表SQL> select deptno,emp.empno,ename,job,loc from emp join dept on (emp.deptno=dept.deptno);
select deptno,emp.empno,ename,job,loc from emp join dept on (emp.deptno=dept.deptno)
ORA-00918: 未明确定义列
SQL> select emp.deptno,emp.empno,ename,job,loc from emp join dept on (emp.deptno=dept.deptno);(要指明deptno属于哪个表)
DEPTNO EMPNO ENAME JOB LOC
------ ----- ---------- --------- -------------
10 111 aa NEW YORK
10 7934 MILLER CLERK NEW YORK
10 7839 KING PRESIDENT NEW YORK
10 7782 CLARK MANAGER NEW YORK
20 7566 JONES MANAGER DALLAS
20 7369 SMITH CLERK DALLAS
20 7902 FORD ANALYST DALLAS
20 7876 ADAMS CLERK DALLAS
20 7788 SCOTT ANALYST DALLAS
30 7499 ALLEN SALESMAN CHICAGO
30 7844 TURNER SALESMAN CHICAGO
30 7900 JAMES CLERK CHICAGO
30 7521 WARD SALESMAN CHICAGO
30 7698 BLAKE MANAGER CHICAGO
30 7654 MARTIN SALESMAN CHICAGO
15 rows selected