SQL Joins with On or Using

I recently wrote a post about inner and outer joins, and a couple of people asked what the difference is between USING and ON.

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);
+-------+--------+---------+
| 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)

OK so it's a super-simple tip but until you see the different approaches laid out side-by-side, it can be confusing. This USING trick is why you will often see fields named, for example, "user_id" when they are in the "users" table - then the shorthand can be used any time you join this user_id to any other user_id column.