[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语句就明白为什么了。