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.
Wow. I never realised that. Thanks Lorna!
Pingback: SQL Joins with On or Using | MySQL | Syngu
Indeed i really never used the “USING” shortcut, thanks for the tip!
It seems to be interesting. But it might need the same column name in both tables ,which are related by the statement. Is it right?
Correct, the column names must be identical
How to make it work if the column names are different in both the tables?
If the column names are different, use the ON syntax. USING is a shorthand for if the column names are the same
nice . i havent used Using in my SQL queries
Good to know, thanks.
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’
You are quite right. The ON clause can take multiple parts, the USING is literally a shortcut for two tables where the column names are the same
You can, however, use MULTIPLE column names with JOIN…USING, as long as both are present in the left hand and right hand side tables (at least in PostreSQL):
http://www.postgresql.org/docs/9.2/static/sql-select.html
… and you can state NATURAL JOIN if *all* of the columns should match, e.g. if doing a self-join.
Is it worth pointing out that although supported by MySQL, the USING construct isn’t universal? SQL Server, for instance, doesn’t understand.
Definitely worth pointing that out, thanks Richy!
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! ;)
great
thanks so much
Meu, show de bola! Muito bem explicado, muito obrigado!
Thanks for post it really helped.
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.
After reading this blog i understand join, on very fast.
Thank you!q
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)
Does anyone have any idea what happens if you’ve already joined a secondary table and you then join a third with the USING clause on a field name that exists in both the primary and secondary table? Will it join to both?