[20241225]ANSI JOIN语法问题.txt

[20241225]ANSI JOIN语法问题.txt --//我个人并不喜欢ANSI JOIN的语法，我自己可以发现一个规律，刚毕业大部分喜欢使用ANSI JOIN语法，而有一定年头的开发人员使用 --//+的语法。国外许多开发人员喜欢使用ANSI JOIN，我觉得与英语作为他们的母语有关。正好有人问题这类问题，通过例子给出自己的 --//解析。 1.环境： SCOTT@book01p> @ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.问题： SCOTT@book01p> SELECT * FROM emp INNER JOIN dept USING ( deptno ); DEPTNO EMPNO ENAME  JOB        MGR HIREDATE             SAL COMM DNAME      LOC ------ ----- ------ --------- ---- ------------------- ---- ---- ---------- --------     10  7782 CLARK  MANAGER   7839 1981-06-09 00:00:00 2450      ACCOUNTING NEW YORK     10  7839 KING   PRESIDENT      1981-11-17 00:00:00 5000      ACCOUNTING NEW YORK     10  7934 MILLER CLERK     7782 1982-01-23 00:00:00 1300      ACCOUNTING NEW YORK     20  7566 JONES  MANAGER   7839 1981-04-02 00:00:00 2975      RESEARCH   DALLAS     20  7902 FORD   ANALYST   7566 1981-12-03 00:00:00 3000      RESEARCH   DALLAS     20  7876 ADAMS  CLERK     7788 1987-07-13 00:00:00 1100      RESEARCH   DALLAS     20  7369 SMITH  CLERK     7902 1980-12-17 00:00:00  800      RESEARCH   DALLAS     20  7788 SCOTT  ANALYST   7566 1987-07-13 00:00:00 3000      RESEARCH   DALLAS     30  7521 WARD   SALESMAN  7698 1981-02-22 00:00:00 1250  500 SALES      CHICAGO     30  7844 TURNER SALESMAN  7698 1981-09-08 00:00:00 1500    0 SALES      CHICAGO     30  7499 ALLEN  SALESMAN  7698 1981-02-20 00:00:00 1600  300 SALES      CHICAGO     30  7900 JAM/ES  CLERK     7698 1981-12-03 00:00:00  950      SALES      CHICAGO     30  7698 BLAKE  MANAGER   7839 1981-05-01 00:00:00 2850      SALES      CHICAGO     30  7654 MARTIN SALESMAN  7698 1981-09-28 00:00:00 1250 1400 SALES      CHICAGO 14 rows selected. --//使用using。 SCOTT@book01p> @hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 3536350277 59v6y339chv25            0      27717       844388907  d2c86c45  2024-12-25 17:01:43    16777218 SCOTT@book01p> @ expand_sql_text 59v6y339chv25 SELECT "A1"."DEPTNO_0" "DEPTNO","A1"."EMPNO _1" "EMPNO","A1"."ENAME_2" "ENAME","A1"."JOB_3" "JOB","A1"."MGR_4" "MGR","A1"."HIREDATE_5" "HIREDATE","A1"."SAL_6" "SAL","A1"."COMM_7" "COMM","A1"."DNAME_8" "DNAME","A1"."LOC_9" "LOC" FROM  (SELECT "A2"."DEPTNO" "DEPTNO_0","A3"."EMPNO" "EMPNO _1","A3"."ENAME" "ENAME_2","A3"."JOB" "JOB_3","A3"."MGR" "MGR_4","A3"."HIREDATE" "HIREDATE_5","A3"."SAL" "SAL_6","A3"."COMM" "COMM_7","A2"."DNAME" "DNAME_8","A2"."LOC" "LOC_9" FROM "SCOTT"."EMP" "A3","SCOTT"."DEPT" "A2" WHERE "A3"."DEPTNO"="A2"."DEPTNO") "A1" PL/SQL procedure successfully completed. --//格式化如下： SELECT "A1"."DEPTNO_0" "DEPTNO","A1"."EMPNO _1" "EMPNO",        "A1"."ENAME_2" "ENAME","A1"."JOB_3" "JOB","A1"."MGR_4" "MGR",        "A1"."HIREDATE_5" "HIREDATE","A1"."SAL_6" "SAL","A1"."COMM_7" "COMM",        "A1"."DNAME_8" "DNAME","A1"."LOC_9" "LOC"   FROM (SELECT "A2"."DEPTNO" "DEPTNO_0",        "A3"."EMPNO" "EMPNO _1","A3"."ENAME" "ENAME_2","A3"."JOB" "JOB_3",        "A3"."MGR" "MGR_4","A3"."HIREDATE" "HIREDATE_5","A3"."SAL" "SAL_6",        "A3"."COMM" "COMM_7","A2"."DNAME" "DNAME_8",        "A2"."LOC" "LOC_9" FROM "SCOTT"."EMP" "A3",        "SCOTT"."DEPT" "A2" WHERE "A3"."DEPTNO" = "A2"."DEPTNO") "A1" --//实际上Oracle在内部展开并不使用ansi join语法。 --//如果写成如下,执行报错。 SCOTT@book01p> SELECT emp.deptno,emp.ename FROM emp INNER JOIN dept USING ( deptno ); SELECT emp.deptno,emp.ename FROM emp INNER JOIN dept USING ( deptno )        * ERROR at line 1: ORA-25154: column part of USING clause cannot have qualifier SCOTT@book01p> SELECT dept.deptno,emp.ename FROM emp INNER JOIN dept USING ( deptno ); SELECT dept.deptno,emp.ename FROM emp INNER JOIN dept USING ( deptno )        * ERROR at line 1: ORA-25154: column part of USING clause cannot have qualifier --//只要不加别名才可以执行。 SCOTT@book01p> SELECT deptno,emp.ename FROM emp INNER JOIN dept USING ( deptno );     DEPTNO ENAME ---------- ----------         20 SMITH         30 ALLEN         30 WARD         20 JONES         30 MARTIN         30 BLAKE         10 CLARK         20 SCOTT         10 KING         30 TURNER         20 ADAMS         30 JAMES         20 FORD         10 MILLER 14 rows selected. --//只要看看前面展开的sql语句就明白为什么了。