Both USING and ON will work in the same way in JOINS. It combines the two tables and returns the matched records from both. However, there are differences.

The more versatile of the two is ON.

A column, a group of columns, or even a condition can be used to join tables using ON. When both tables have a column with the exact same name on which to connect, USING is advantageous.

Any columns listed in the USING will only appear once, with an unqualified name, as opposed to once for each table in the join. In join, you will see the same column twice. This means the column DeptID will appear only once instead of Emp.DeptID and Dept.DeptID when specifying the keyword USING. You can also use USING(BatchID, StudentID) which is helpful when joining by composite main keys.

The condition is the column names must be the same and should exist in both tables when you use USING keyword. The “Using” keyword only aids in natural or equivalent joins and only returns records that match. However, ON is capable of much more comparisons than just utilizing the equals sign. BETWEEN.. AND, greater than or less than, etc. are a few examples.

SELECT * FROM Customers
LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID AND Customers.Age BETWEEN 18 AND 25;

SELECT customerID, OrderID
FROM Customers
JOIN Orders USING (CustomerID)

Hope you find this article useful.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s