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.

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.

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.