In a nutshell, you use ON for most things, but USING is a handy shorthand for the situation where the column names are the same.
Consider this example dataset:
mysql> select * from pets;
+---------+---------+--------+-----------+
| pets_id | animal | name | owners_id |
+---------+---------+--------+-----------+
| 1 | fox | Rusty | 2 |
| 2 | cat | Fluffy | 2 |
| 3 | cat | Smudge | 3 |
| 4 | cat | Toffee | 3 |
| 5 | dog | Pig | 3 |
| 6 | hamster | Henry | 1 |
| 7 | dog | Honey | 1 |
+---------+---------+--------+-----------+
7 rows in set (0.00 sec)
mysql> select * from owners;
+-----------+-------+
| owners_id | name |
+-----------+-------+
| 1 | Susie |
| 2 | Sally |
| 3 | Sarah |
+-----------+-------+
3 rows in set (0.00 sec)
To find out who has which pets, we would join the two tables together like this:
mysql> select owners.name as owner, pets.name as pet, pets.animal
-> from owners join pets on (pets.owners_id = owners.owners_id);
+-------+--------+---------+
| owner | pet | animal |
+-------+--------+---------+
| Sally | Rusty | fox |
| Sally | Fluffy | cat |
| Sarah | Smudge | cat |
| Sarah | Toffee | cat |
| Sarah | Pig | dog |
| Susie | Henry | hamster |
| Susie | Honey | dog |
+-------+--------+---------+
7 rows in set (0.00 sec)
The example above uses the ON keyword, but since the columns we use to join are called owners_id in both tables, then we can instead put in USING as a shorthand.
mysql> select owners.name as owner, pets.name as pet, pets.animal
-> from owners join pets using (owners_id);
Here are the different types of the JOINs in SQL:
- (INNER) JOIN: Returns records that have matching values in both tables
- LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
- FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
