Does MySQL allow using syntax with straight join?

Question

I'm trying to convert my query from inner join to straight join. My original query was: SELECT COUNT(*) FROM x AS t1 INNER JOIN x AS t2 USING (id); And I'd like to simply replace inner join to straight join: SELECT COUNT(*) FROM x AS t1 STRAIGHT_JOIN x AS t2 USING (id);

The first query executes as expected but the second query throws syntax error. If I change the query to simply SELECT COUNT(*) FROM x AS t1 STRAIGHT_JOIN x AS t2 ON t1.id=t2.id

Does MySQL disallow straight join with using? I didn't see that in the docs.

http://stackoverflow.com/a/516720/1190388 – hjpotter92 Nov 18 '14 at 22:03 — hjpotter92, Nov 18 '14 at 22:03

score 1 · Answer 1 · answered Nov 18 '14 at 22:04

1

I think you can go for NATURAL JOIN in MySQL like

SELECT COUNT(*) FROM x AS t1 NATURAL JOIN x AS t2;

answered Nov 18 '14 at 22:04

Rahul

76,197
13
71
125

score 1 · Answer 2 · answered Nov 18 '14 at 22:04

1

Quoting the reference manual for JOIN syntax:

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

answered Nov 18 '14 at 22:04

Barranka

20,547
13
65
83

Bill Karwin · Accepted Answer · 2014-11-18T22:28:16.953

Yes, MySQL disallows straight join with USING. The syntax rules in http://dev.mysql.com/doc/refman/5.6/en/join.html show this:

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)

You can see that the syntax with STRAIGHT_JOIN allows only no condition, or an ON clause. Other joins types allow [join_condition] which is another syntax rule that expands to either ON clause or aUSING expression.

I don't know if there's a good reason for this different treatment of STRAIGHT_JOIN. Perhaps it was simply an oversight. For what it's worth, this was reported as a bug back in 2008: http://bugs.mysql.com/bug.php?id=35268 But there's no explanation for the difference, and no sign that it has any priority to get fixed.

As a workaround, you could apply the STRAIGHT_JOIN query modifier to the whole query, and then use standard JOIN operators:

SELECT STRAIGHT_JOIN COUNT(*) FROM x AS t1 INNER JOIN x AS t2 USING (id)

Does MySQL allow using syntax with straight join?

3 Answers3