What is the difference between ON and USING() when JOINing two or more tables together in a SQL query?
For example you have the following two tables, Users and Products, when a Product is created, the Users id that created it is stored.
| Products table: | |
|---|---|
| id | Primary Key |
| created_by_user_id | Foreign Key to Users table |
| product_name |
| Users table: | |
|---|---|
| id | Primary Key |
| first_name | |
| last_name |
If you want to find out who created the product, you can do that easily with a simple SQL query like this:
SELECT p.*, u.*
FROM products p
INNER JOIN users u ON p.created_by_user_id = u.id
If however the user id field was named the same in both tables like this:
| Products table: | |
|---|---|
| id | Primary Key |
| user_id | Foreign Key to Users table |
| product_name |
| Users table: | |
|---|---|
| user_id | Primary Key |
| first_name | |
| last_name |
You can then use USING to JOIN the tables together and return the same data with this SQL:
SELECT p.*, u.*
FROM products p
INNER JOIN users u USING(user_id)
Each time you JOIN a table you will need to use an additional USING clause even if the second and third tables contain the exactly same column name.
Make sure that when you are JOINing tables together using the ON or the USING clause that the data type of the matching columns is the same data type.
Interested in more SQL tips? Please leave a comment below or pop your email in the box below and you’ll be sure to receive them!