Difference between USING and ON in Oracle SQL

Question

There seems to be no 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, besides ON I can use different column names)

Answer 1

The difference for me is that you can draw yourself in the corner using 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 selection, you get ora-25154, "part of the USING condition column cannot have a qualifier."

If you remove the e1.empid classifier, 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 error ORA-00918, "The column is ambiguous."

You should 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 first studied the syntax of the connection, I ran into this exact problem in a real situation. Since then I have avoided the USING clause. There is no benefit with the USING clause, except for a few keystrokes.

Answer 2

Little. Of course, ON allows this, in which USE does not work:

 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 3

ON includes all columns that join from join tables, so you have two or more of the same column types in the resulting dataset.

But USING removes duplicate columns that join to leave one of them, making it look like a natural join (except that they don't have to have the same name) as highlighted in this question about natural joins

USING may be useful;) to perform insertions with a subquery that joins the tables (in my case, table alignment for the data warehouse asterisk scheme, where I need only one of the id columns).

Answer 4

No. If you leave the syntax behind, there is no difference in these exact two sentences.