USING clause in oracle 11g

Question

 select e.employee_id,e.last_name,e.department_id,
         d.department_name,l.city,l.location_id
 from employees e 
 join departments d
      on e.department_id=d.department_id
 join locations l
      on l.location_id=d.location_id
 and e.manager_id=149;

Can we Replace 'ON' clause with 'USING' clause. -used Employees,Departments,Locations table in oracle 11g. Try It.

Yaroslav Shabalin · Accepted Answer · 2013-12-26 20:57:25Z

2

No you cannot just replace ON with USING. But you can rewrite the query to contain USING clause in joins. See correct syntax below:

select e.employee_id,
       e.last_name,
       department_id, --Note there is no table prefix
       d.department_name,
       l.city,
       location_id --Note there is no table prefix
  from employees e
  join departments d
 using (department_id)
  join locations l
 using (location_id)
 where e.manager_id = 149;

answered Dec 26, 2013 at 20:57

Yaroslav Shabalin

1,6443 gold badges17 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

A B · Accepted Answer · 2013-12-26 22:52:12Z

2

The USING clause can be substituted for the ON clause, but just replacing USING with ON is not sufficient.

The USING clause doesn't use the equals sign syntax, so the replacement cannot be done word for word
This URL states the USING clause can be used in place of the ON clause; but this refers to the CLAUSES, not to only the keywords:

http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqljusing.html
The syntax of the USING clause would be:
```
      select * from table1 P join table1 S
      using (col1);
```
Here col1 is present in both table1 and table2.
You can't use a table prefix in the USING clause as you can in the ON clause (you can't use P.col1 in a USING statement in ORACLE).

edited Dec 26, 2013 at 22:52

answered Dec 26, 2013 at 19:57

A B

4,1581 gold badge22 silver badges23 bronze badges

Comments

Maheswaran Ravisankar · Accepted Answer · 2013-12-26 22:38:22Z

My two cents.

USING CLAUSE :

When several columns share the same name and you make a NATURAL JOIN. Then you may not want to join all such columns. In that case, you can explicitly specify just the column names that has to be used as the join key!

ON CLAUSE:

When the column names are different in the joining tables, we tend to specify exactly, the column names in respective tables, to be used as JOIN keys.

But when seeing behind the screen, the internal working is going to be the same. It is all how comfortable we are with the syntax.

Say, some day the column names are changed (Ofcourse it is a bad model), but still, you queries may fail with a USING clause. So, to play safe, ON clause is always advisable! Depends on the individual opinion.!

Good luck!

Collectives™ on Stack Overflow

USING clause in oracle 11g

3 Answers 3

Comments

Comments

USING CLAUSE :

ON CLAUSE:

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

USING CLAUSE :

ON CLAUSE:

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related