Oracle using Keyword

Oracle using Keyword
Oracle using Keyword
The SQL/92 standard can use the using keyword to simplify the connection query, but only when the query meets the following two conditions

Simplify with the using keyword.
1. the query must be an equijoin.
2. columns in the equijoin must have the same name and data type.
For example, use the using Keyword, as follows:

select emptno,ename,sal,deptno,dname from emp e inner join dept d using(deptno);SQL> select e.empno,e.ename,e.sal,deptno,d.dname from2 emp e inner join dept d using(deptno);EMPNO ENAME SAL DEPTNO DNAME---------- ---------- ---------- ---------- --------------7369 SMITH 800 20 RESEARCH7499 ALLEN 1600 30 SALES7521 WARD 1250 30 SALES7566 JONES 2975 20 RESEARCH7654 MARTIN 1250 30 SALES7698 BLAKE 2850 30 SALES7782 CLARK 2450 10 ACCOUNTING7788 SCOTT 3000 20 RESEARCH7839 KING 5000 10 ACCOUNTING7844 TURNER 1500 30 SALES7876 ADAMS 1100 20 RESEARCH7900 JAMES 1800 30 SALES7902 FORD 3000 20 RESEARCH7934 MILLER 1300 10 ACCOUNTING7935 XIAOXUE 5000 20 RESEARCH

15 rows have been selected.
If the above results are the same as those of the natural connection.

Note the following when using the using keyword to simplify the connection:
1. When using the deptno columns in the emp and dept tables to connect, neither the using Clause nor the select clause can specify the table name or table name for the deptno column.
2. If you use the same multiple columns in two tables during connection query, you can specify multiple column names in the using clause for a long time. The format is as follows:

select... from table1 inner join table2using(column1,column2)

The preceding statement is equivalent to the following statement:

select... from table1 inner join table2on table1.column1=table2.column2and table1.column2=table2.column2;

If you search for multiple tables, you must use the using Keyword multiple times. The format is as follows:

select... from table1inner join table2 using(column1)inner join table3 using(column2);

The preceding statement is equivalent to the following statement:

select... from table1,table2,table3where table1.column1=table2.column1and table2.column2=table3.table2;