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.

21 thoughts on “SQL Joins with On or Using

  1. Pingback: SQL Joins with On or Using | MySQL | Syngu

  2. Unfortunately you can't use additional joining conditions after USING like you do with ON, the following syntax is invalid:

    (...)
    FROM left AS l
    INNER JOIN right AS r USING (id) AND l.something > 'something else'

  3. I had not run across the USING keyword before...
    I found your blog entry via Google and I appreciate the clear and concise tutorial. Thanks for posting this! ;)

  4. Can you please tell the difference between the where clause and ON clause and can we use where clause instead of ON clause and wil they both work equally.

  5. Late notes as this is the first hit on "SQL JOIN USING" in my bubble:
    #1 There are also NATURAL JOINs, where you don't even have to specify the columns to join on. It'll just join on the columns with common names. But that requires that you not only use the same names in your data model for columns that you want to join on, but also different ones for columns that you don't accidentally want to join on - eg. by generally using a table specific prefix (just like "user_id" from the example above). So even more convinient than USING - but at a much larger price.
    #2 USING works at least with PostgreSQL, Oracle and (MariaDB|MySQL)

Mentions

Leave a Reply

Please use [code] and [/code] around any source code you wish to share.

This site uses Akismet to reduce spam. Learn how your comment data is processed.