difference between ON Clause and using clause in sql

• 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, cityfrom departmentsJOIN locationsUSING (location_id); -- specify the same column name                      -- for both of the tables for the joinselect department_name, cityfrom departments deptjoin locations locon (dept.location_id = loc.id); -- specify different column name                                 -- for the tables for the join.

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.

Additional to above answers.

using Clause will print joined column just once.

A.id  B.id1      12      23      3

Select * from A JOIN B using(id);

Output will be

id123

But in On clause

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

Output will be.

id     id1      12      23      3