MySQL JOIN ON vs USING

Question

In a MySQL JOIN  what is the difference between ON and USING    As far as I can tell  USING   is just more convenient syntax  whereas ON allows a little more flexibility when the column names are not identical  However  that difference is so minor  you d think they d just do away with USING     Is there more to this than meets the eye  If yes  which should I use in a given situation

User · Answer

For those experimenting with this in phpMyAdmin, just a word:

phpMyAdmin appears to have a few problems with USING. For the record this is phpMyAdmin run on Linux Mint, version: "4.5.4.1deb2ubuntu2", Database server: "10.2.14-MariaDB-10.2.14+maria~xenial - mariadb.org binary distribution".

I have run SELECT commands using JOIN and USING in both phpMyAdmin and in Terminal (command line), and the ones in phpMyAdmin produce some baffling responses:

1) a LIMIT clause at the end appears to be ignored.
2) the supposed number of rows as reported at the top of the page with the results is sometimes wrong: for example 4 are returned, but at the top it says "Showing rows 0 - 24 (2503 total, Query took 0.0018 seconds.)"

Logging on to mysql normally and running the same queries does not produce these errors. Nor do these errors occur when running the same query in phpMyAdmin using JOIN ... ON .... Presumably a phpMyAdmin bug.

User · Answer

Thought I would chip in here with when I have found ON to be more useful than USING  It is when OUTER joins are introduced into queries   ON benefits from allowing the results set of the table that a query is OUTER joining onto to be restricted while maintaining the OUTER join  Attempting to restrict the results set through specifying a WHERE clause will  effectively  change the OUTER join into an INNER join   Granted this may be a relative corner case  Worth putting out there though       For example   CREATE TABLE country      countryId int 10  unsigned NOT NULL PRIMARY KEY AUTO INCREMENT     country varchar 50  not null    UNIQUE KEY countryUIdx1  country    ENGINE InnoDB   insert into country country  values   France    insert into country country  values   China    insert into country country  values   USA    insert into country country  values   Italy    insert into country country  values   UK    insert into country country  values   Monaco      CREATE TABLE city     cityId int 10  unsigned NOT NULL PRIMARY KEY AUTO INCREMENT    countryId int 10  unsigned not null    city varchar 50  not null    hasAirport boolean not null default true    UNIQUE KEY cityUIdx1  countryId city     CONSTRAINT city country fk1 FOREIGN KEY  countryId  REFERENCES country  countryId    ENGINE InnoDB    insert into city  countryId city hasAirport  values  1  Paris  true   insert into city  countryId city hasAirport  values  2  Bejing  true   insert into city  countryId city hasAirport  values  3  New York  true   insert into city  countryId city hasAirport  values  4  Napoli  true   insert into city  countryId city hasAirport  values  5  Manchester  true   insert into city  countryId city hasAirport  values  5  Birmingham  false   insert into city  countryId city hasAirport  values  3  Cincinatti  false   insert into city  countryId city hasAirport  values  6  Monaco  false    -- Gah  Left outer join is now effectively an inner join  -- because of the where predicate select   from country left join city using  countryId  where hasAirport     -- Hooray  I can see Monaco again thanks to  -- moving my predicate into the ON select   from country co left join city ci on  co countryId ci countryId and ci hasAirport

User · Answer

Wikipedia has the following information about USING      The USING construct is more than mere syntactic sugar  however  since   the result set differs from the result set of the version with the   explicit predicate  Specifically  any columns mentioned in the USING   list will appear only once  with an unqualified name  rather than once   for each table in the join  In the case above  there will be a single   DepartmentID column and no employee DepartmentID or   department DepartmentID    Tables that it was talking about     The Postgres documentation also defines them pretty well      The ON clause is the most general kind of join condition  it takes a   Boolean value expression of the same kind as is used in a WHERE   clause  A pair of rows from T1 and T2 match if the ON expression   evaluates to true       The USING clause is a shorthand that allows you to take advantage of   the specific situation where both sides of the join use the same name   for the joining column s   It takes a comma-separated list of the   shared column names and forms a join condition that includes an   equality comparison for each one  For example  joining T1 and T2 with   USING  a  b  produces the join condition ON T1 a   T2 a AND T1 b     T2 b       Furthermore  the output of JOIN USING suppresses redundant columns    there is no need to print both of the matched columns  since they must   have equal values  While JOIN ON produces all columns from T1 followed   by all columns from T2  JOIN USING produces one output column for each   of the listed column pairs  in the listed order   followed by any   remaining columns from T1  followed by any remaining columns from T2

User · Answer

It is mostly syntactic sugar  but a couple differences are noteworthy   ON is the more general of the two  One can join tables ON a column  a set of columns and even a condition  For example   SELECT   FROM world City JOIN world Country ON  City CountryCode   Country Code  WHERE       USING is useful when both tables share a column of the exact same name on which they join  In this case  one may say   SELECT     FROM film JOIN film actor USING  film id  WHERE       An additional nice treat is that one does not need to fully qualify the joining columns   SELECT film title  film id -- film id is not prefixed FROM film JOIN film actor USING  film id  WHERE       To illustrate  to do the above with ON  we would have to write   SELECT film title  film film id -- film film id is required here FROM film JOIN film actor ON  film film id   film actor film id  WHERE       Notice the film film id qualification in the SELECT clause  It would be invalid to just say film id since that would make for an ambiguity      ERROR 1052  23000   Column  film id  in field list is ambiguous   As for select    the joining column appears in the result set twice with ON while it appears only  once with USING   mysql gt  create table t i int  insert t select 1 create table t2 select from t  Query OK  0 rows affected  0 11 sec   Query OK  1 row affected  0 00 sec  Records  1  Duplicates  0  Warnings  0  Query OK  1 row affected  0 19 sec  Records  1  Duplicates  0  Warnings  0  mysql gt  select from t join t2 on t i t2 i   ------ ------    i      i       ------ ------       1      1    ------ ------  1 row in set  0 00 sec   mysql gt  select from t join t2 using i    ------    i       ------       1    ------  1 row in set  0 00 sec   mysql gt

User · Answer

Database tables To demonstrate how the USING and ON clauses work  let s assume we have the following post and post comment database tables  which form a one-to-many table relationship via the post id Foreign Key column in the post comment table referencing the post id Primary Key column in the post table   The parent post table has 3 rows    post id   title        --------- -----------    1         Java          2         Hibernate     3         JPA          and the post comment child table has the 3 records    post comment id   review      post id    ----------------- ----------- ---------    1                 Good        1           2                 Excellent   1           3                 Awesome     2          The JOIN ON clause using a custom projection Traditionally  when writing an INNER JOIN or LEFT JOIN query  we happen to use the ON clause to define the join condition  For example  to get the comments along with their associated post title and identifier  we can use  the following SQL projection query  SELECT    post post id     title     review FROM post INNER JOIN post comment ON post post id   post comment post id ORDER BY post post id  post comment id  And  we get back the following result set    post id   title       review       --------- ----------- -----------    1         Java        Good          1         Java        Excellent     2         Hibernate   Awesome      The JOIN USING clause using a custom projection When the Foreign Key column and the column it references have the same name  we can use the USING clause  like in the following example  SELECT   post id    title    review FROM post INNER JOIN post comment USING post id  ORDER BY post id  post comment id  And  the result set for this particular query is identical to the previous SQL query that used the ON clause    post id   title       review       --------- ----------- -----------    1         Java        Good          1         Java        Excellent     2         Hibernate   Awesome       The USING clause works for Oracle  PostgreSQL  MySQL  and MariaDB  SQL Server doesn t support the USING clause  so you need to use the ON clause instead  The USING clause can be used with INNER  LEFT  RIGHT  and FULL JOIN statements   SQL JOIN ON clause with SELECT   Now  if we change the previous ON clause query to select all columns using SELECT    SELECT   FROM post INNER JOIN post comment ON post post id   post comment post id ORDER BY post post id  post comment id  We are going to get the following result set    post id   title       post comment id   review      post id    --------- ----------- ----------------- ----------- ---------    1         Java        1                 Good        1           1         Java        2                 Excellent   1           2         Hibernate   3                 Awesome     2           As you can see  the post id is duplicated because both the post and post comment tables contain a post id column   SQL JOIN USING clause with SELECT   On the other hand  if we run a SELECT   query that features the USING clause for the JOIN condition  SELECT   FROM post INNER JOIN post comment USING post id  ORDER BY post id  post comment id  We will get the following result set    post id   title       post comment id   review       --------- ----------- ----------------- -----------    1         Java        1                 Good          1         Java        2                 Excellent     2         Hibernate   3                 Awesome       You can see that this time  the post id column is deduplicated  so there is a single post id column being included in the result set   Conclusion If the database schema is designed so that Foreign Key column names match the columns they reference  and the JOIN conditions only check if the Foreign Key column value is equal to the value of its mirroring column in the other table  then you can employ the USING clause  Otherwise  if the Foreign Key column name differs from the referencing column or you want to include a more complex join condition  then you should use the ON clause instead

[mysql] MySQL JOIN ON vs USING?

Examples related to mysql

Examples related to join

Examples related to using