Search code examples
sqlmysqljoininner-joinusing

Can MySQL / SQL's short hand of "Using" be used without saying "Inner Join"?

The following 2 statements are to join using gifts.giftID = sentgifts.giftID:

mysql> select * from gifts, sentgifts using (giftID);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'using
 (giftID)' at line 1

and the second one:

mysql> select * from gifts INNER JOIN sentgifts using (giftID);
+--------+------------+----------------+---------------------+--------+------------+--------+------+---------------------+
| giftID | name       | filename       | effectiveTime       | sentID | whenSent   | fromID | toID | trytryWhen          |
+--------+------------+----------------+---------------------+--------+------------+--------+------+---------------------+
|      2 | teddy bear | bear.jpg       | 2010-04-24 04:36:03 |      4 | 2010-04-24 |   NULL |  111 | 2010-04-24 03:10:42 |
|      6 | beer       | beer_glass.png | 2010-04-24 05:18:12 |      5 | 2010-03-03 |     11 |   22 | 2010-03-03 00:00:00 |
|      6 | beer       | beer_glass.png | 2010-04-24 05:18:12 |      6 | 2010-04-24 |     11 |  222 | 2010-04-24 03:54:49 |
|      6 | beer       | beer_glass.png | 2010-04-24 05:18:12 |      7 | 2010-04-24 |      1 |    2 | 2010-04-24 03:58:45 |
+--------+------------+----------------+---------------------+--------+------------+--------+------+---------------------+
4 rows in set (0.00 sec)

Can the first statement also use the "using" shorthand? It seems that when it is used then the word "Inner Join" must be specified... but the first statement is actually an inner join?

Update: if it can't be done, is there a reason why? The first statement actually is all clear enough what is meant to be done... is there a reason why it is prohibited?

Solution

  • It can not. You have to use the JOIN keyword between the tables you want to join, in order for USING to work.

    Here is the relevant grammar, from the MySQL manual:

    join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON conditional_expr
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

join_condition:
    ON conditional_expr
  | USING (column_list)

    Also, it is not that clear what you are trying to accomplish with the first syntax you are using. It could be an outer or an inner join. You can argue that, when absent, the interpreter should consider it an inner join, but that would be confusing. Additionally, it would be a new, non-standard syntax, which is definitely a no no, imho.