difference between ON Clause and using clause in sql

Question

I am doing some assignment related to joins in oracle. In some point I stucked i.e. what is the difference between USING and ON clause.

I know that using ON clause we are able to join unlimited tables. Is it possible to join unlimited tables using USING clause?how? could you explain it by using example.

Orvalorvan

The documentation is pretty good – Alphaalphabet

I'd disagree with @MaximKrizhanovsky and say the documentation is pretty unclear. It doesn't say if they are equivalent or what differences occur. – Belcher

Answer 1

• The USING clause: This allows you to specify the join key by name.

• The ON clause: This syntax allows you to specify the column names for join keys in both tables.

The USING clause

The USING clause is used if several columns share the same name but you don’t want to join using all of these common columns. The columns listed in the USING clause can’t have any qualifiers in the statement, including the WHERE clause:

The ON clause

The ON clause is used to join tables where the column names don’t match in both tables. The join conditions are removed from the filter conditions in the WHERE clause:

Oracle

select department_name, city
from departments
JOIN locations
USING (location_id); -- specify the same column name 
                     -- for both of the tables for the join
select department_name, city
from departments dept
join locations loc
on (dept.location_id = loc.id); -- specify different column name 
                                -- for the tables for the join.

Shortly

Answer 2

In addition to the answers above, an important difference is that the ON clause preserves the columns from each joined table separately, which the USING clause merges the columns from the joined tables into a single column. This can be important if, for example, you want to retain rows in your result set only if a matching row does not exist in one of the joined tables. To do this you'd typically use an OUTER JOIN along with a condition in the WHERE clause, such as

SELECT t1.*
  FROM TABLE_1 t1
  LEFT OUTER JOIN TABLE_2 t2
    ON (t2.KEY_FIELD = t1.KEY_FIELD)
  WHERE t2.KEY_FIELD IS NULL

In this case, the assumption is that TABLE_2.KEY_FIELD is part of the primary key on TABLE_2, and thus can never be NULL if data is actually present in TABLE_2. If, after the above join, TABLE_2.KEY_FIELD is found to contain NULL in the joined set, it means that no TABLE_2 row was found to match the corresponding TABLE_1 row. This can sometimes be useful.

Share and enjoy.

Berezina

Answer 3

Additional to above answers.

using Clause will print joined column just once.

A.id  B.id
1      1
2      2
3      3

Select * from A JOIN B using(id);

Output will be

id
1
2
3

But in On clause

Select * from A JOIN B on A.id=B.id;

Output will be.

id     id
1      1
2      2
3      3

Feodora

Answer 4

Both allow joining of "unlimited" tables. The difference is that USING requires the join columns to have the same name:

select emp.ename, dept.dname
from emp join dept using (deptno);

The ON version works also when the join columns have different names:

select emp.ename, emp2.ename manager_name
from emp join emp emp2 on (emp.mgr = emp2.empno);

Skean

My comment was wrong re Oracle SQL, although correct re standard SQL. I meant that select * from x join y using (c1,...) cannot equal select * from x join y on x.c1=y.c1 and ... since it has fewer columns & so your "the difference is" wouldn't be correct, though "a difference is" would be. But in Oracle SQL there's still a difference with how * & undotted USING columns are treated in SELECT. Also that sentence of yours is not clear. Not that it's clear what the question is trying to ask. – Mcglothlin

Answer 5

USING clause:

SELECT * FROM COUNTRIES JOIN CITIES USING (COUNTRY)

The above query performs an inner join between the COUNTRIES table and the CITIES table on the condition that COUNTRIES.COUNTRY is equal to CITIES.COUNTRY

ON clause:

SELECT * FROM COUNTRIES JOIN CITIES ON (COUNTRIES.COUNTRY = CITIES.COUNTRY)

The above query perform inner join operation using on clause.

Sanfordsanfourd

Answer 6

SELECT s.SID, s.SNAME, a.CNAME, c.MAJOR
FROM STUDENT s JOIN COLLEGE c
ON s.SID = c.SID;

SELECT SID, s.SNAME, a.CNAME, a.MAJOR
FROM STUDENT s JOIN COLLEGE c
USING (SID);

The USING clause: This allows to specify the join key by name.

The ON allows to specify the column names for join keys in both tables.

Hedrick

You are only answering half the question. – Epigraphy

Answer 7

On clause as well as Using clause does the same work but if column names are same, go for the USING clause. Otherwise, if columns names are not same, go for ON clause.

Crocodile

This answer is not clear. Also please use standard punctuation. – Mcglothlin