The difference between a SELECT statement in a join and an ON statement selects a public column

When you join multiple tables, in a SELECT statement, if you use a using statement, the selected column in the using statement cannot be specified in the SELECT statement, otherwise it will be reported ORA-25154

View the EMP table

sql> select * from emp; empno ename      job          mgr hiredate          sal       comm deptno----- ---------- --------- ----- ----------- --------- --- ------ ------ 7369 SMITH      CLERK       7902 1980/12/17     800.00                20 7499 allen      salesman    7698 1981/2/20     1600.00    300.00      30 7521 WARD       SALESMAN    7698 1981/2/22     1250.00  &nbsP; 500.00     30 7566 jones      manager     7839 1981/4/2      2975.00                20 7654 MARTIN      SALESMAN   7698 1981/9/28     1250.00    1400.00     30 7698 blake      manager     7839 1981/5/1      2850.00                30 7782 CLARK       manager    7839 1981/6/9      2450.00                10 7788  scott      analyst    7566 1987/4/19     3000.00                20 7839 king        PRESIDENT       1981/11/17     5000.00                10 7844 TURNER     SALESMAN   7698 1981/9/8       1500.00      0.00     30  7876 ADAMS      CLERK      7788  1987/5/23     1100.00                20 7900 JAMES      CLERK       7698 1981/12/3      950.00                30 7902 FORD        analyst    7566 1981/12/3     3000.00                20 7934 MILLER      CLERK      7782 1982/1/23      1300.00               10

View Dept Table

Sql> SELECT * from Dept;deptno dname LOC---------------------------------ACCOUNTING NEW YORK 2 0 DALLAS SALES CHICAGO OPERATIONS BOSTON

Use the column specified in the using statement in the SELECT statement to add a qualifier

Sql> Select E.deptno,e.sal,d.dname from emp e join Dept D using (DEPTNO) ora-25154:using clause cannot have a qualifier in the column part

When not added

Sql> select deptno,e.sal,d.dname from emp e join dept d using ( DEPTNO);D eptno       sal dname------ --------- ------------- -    10   2450.00 accounting    10    5000.00 ACCOUNTING    10   1300.00 ACCOUNTING     20   2975.00 RESEARCH    20   3000.00  research    20   1100.00 research    20     800.00 RESEARCH    20   3000.00 RESEARCH     30   1250.00 sales    30   1500.00  SALES    30   1600.00 SALES    30     950.00 sales    30   2850.00 sales    30    1250.00 sales14 rows selected

When using on, you must specify the qualifier to display correctly, or you will get an error, indicating that DEPTNO does not recognize which table, because dept and EMP Tables have DEPTNO columns

Select Deptno,e.sal,d.dname from emp e joins Dept D on (e.deptno=d.deptno), select Deptno,e.sal,d.dname from emp e join Dept D on (E.deptno=d.deptno) ORA-00918: column not explicitly defined

To add qualifiers to DEPTNO, you can display them normally.

Sql> select e.deptno,e.sal,d.dname from emp e join dept d on ( E.DEPTNO=D.DEPTNO);D eptno       sal dname------ --------- -- ------------    10   2450.00 accounting    10    5000.00 accounting    10   1300.00 accounting     20   2975.00 RESEARCH    20    3000.00 research    20   1100.00 research     20    800.00 research    20   3000.00  research    30   1250.00 sales    30    1500.00 SALES    30   1600.00 SALES     30    950.00 sales    30   2850.00 sales    30    1250.00 sales14 rows selected

You do not need to specify a qualifier when using the column specified by the selected using in the SELECT statement

When you use on, you must add qualifiers to the Condition column in the ON statement condition in the SELECT statement.

The difference between a SELECT statement in a join and an ON statement selects a public column