using的用法

来源：互联网发布：大数据分析硕士编辑：程序博客网时间：2024/05/16 14:59

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