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

Question

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?

Answer 1

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.

Answer 2

The using condition is only there to prevent you from having to type it out like this.

SELECT * FROM GIFTS INNER JOIN SENTGIFTS ON GIFTS.giftID = SENTGIFTS.giftID

So it prevents you from having to type out that ON condition. You still have to specify that it is an inner join.

In response to your edit, the reason why it doesn't work is because you aren't specifying what kind of join you want to do. Are you doing an inner joing? Left join? Outer join? The server's SQL interpreter can't assume which one you are using.