In MySQL JOIN, what is the difference between ON
and USING ()
?
From what I can tell, USING()
it's a more convenient syntax, while ON
allowing a bit more flexibility when the column names are not identical. However, that difference is so small that you'd think they'd just remove USING ()
.
Is there more to this than meets the eye? If so, what should you use in a given situation?
It's mostly syntactic sugar, but there are a couple of notable differences:
ON
It is the more general of the two. One can join tables
ON
a column, a set of columns and even a condition .For example:
USING
This is useful when both tables share a column of the exact same name where they are joined .
For example:
A nice bonus is that one doesn't need to fully qualify the join columns:
To illustrate, to do the above with ON, we would have to write:
Note the qualification
film.film_id
in the clauseSELECT
. It would be invalid to simply sayfilm_id
as that would create an ambiguity:As for
SELECT *
, the join column appears in the result set twice with ,ON
while it only appears once withUSING
:Reference: