SQL/92 standard allows you to use USING keyword in Oracle SQL joins to simplify queries. But with the following conditions:
- The query should have equijoin.
- The fields in the equijoin must have the same name.
We use mostly equijoins for queries and if you always use the same name as the primary key for your foreign keys, then you will satisfy the requirements. Below is an example query which uses USING clause instead of ON.
Using Clause in Inner Joins
Select e.name, b.bonus_amt from emp e inner join bonus b using (empno);
If you also want to select empno column in Select statement then specify the column without alias, as shown in below example:
Select e.name, b.bonus_amt, empno from emp e inner join bonus b using (empno);
Using Clause in Left Outer Joins
Select e.name, b.bonus_amt from emp e left outer join bonus b using (empno);
Using Clause in Right Outer Joins
Select e.name, b.bonus_amt from emp e Right outer join bonus b using (empno);
Using Clause in Full Outer Joins
Select e.name, b.bonus_amt from emp e full outer join bonus b using (empno);