Difference between USING and ON in Oracle SQL

Question

It doesn't seem like there's a difference between:

SELECT a.col1, b.col2
  FROM table a JOIN table2 b
 USING (col3)

and

SELECT a.col1, b.col2
  FROM table a JOIN table2 b
    ON (a.col3 = b.col3)

Or is there? (of course other than with ON I can use different column names)

Answer 1

Not much. Of course, ON allows this, which USING does not:

SELECT a.col1, b.col2
  FROM table a JOIN table2 b
    ON (a.col3 = b.col4)
                      ^

This is the ANSI join syntax, it is not unique to Oracle.

Answer 2

The difference for me is that you can paint yourself into a corner with the USING clause:

CREATE TABLE roster (mgrid INTEGER, empid INTEGER);
CREATE TABLE emp (empid INTEGER, NAME VARCHAR2(20));

INSERT INTO roster VALUES (1,10);
INSERT INTO roster VALUES (1,11);
INSERT INTO roster VALUES (1,12);
INSERT INTO roster VALUES (2,20);
INSERT INTO roster VALUES (2,21);

INSERT INTO emp VALUES (10, 'John');
INSERT INTO emp VALUES (11, 'Steve');
INSERT INTO emp VALUES (12, 'Mary');
INSERT INTO emp VALUES (20, 'Ann');
INSERT INTO emp VALUES (21, 'George');
INSERT INTO emp VALUES (1, 'Pete');
INSERT INTO emp VALUES (2, 'Sally');

SELECT r.mgrid, e2.name, e1.empid, e1.name
  FROM roster r JOIN emp e1 USING(empid)
                JOIN emp e2 ON r.mgrid = e2.empid; 

In the above select, you get an ora-25154, "column part of USING clause cannot have a qualifier".

If you remove the e1.empid qualifier, as in:

SELECT r.mgrid, e2.name, empid, e1.name
  FROM roster r JOIN emp e1 USING(empid)
                JOIN emp e2 ON r.mgrid = e2.empid; 

You get an ORA-00918 error, "column ambiguously defined".

You have to use:

SELECT r.mgrid, e2.name, e1.empid, e1.name
  FROM roster r JOIN emp e1 ON r.empid = e1.empid
                JOIN emp e2 ON r.mgrid = e2.empid; 

The example is contrived, but when I was first exploring the join syntax I ran into this exact problem in a real situation. I have avoided the USING clause ever since. There is no advantage with the USING clause other than a few keystrokes.