116

So I've been looking through the internet the last hour, reading and looking for the definitive answer to this simple question.

What is the default JOIN in MySQL?

SELECT * FROM t1 JOIN t2

Is that the same as

SELECT * FROM t1, t2

OR

SELECT * FROM t1 INNER JOIN t2

Also a related question, when you use "WHERE" clauses, is it the same as JOIN or INNER JOIN ?

Right now I'm thinking a stand-alone JOIN is identical to using commas and WHERE clauses.

Stijn de Witt
  • 40,192
  • 13
  • 79
  • 80
Quang Van
  • 11,475
  • 12
  • 57
  • 61

2 Answers2

156

In MySQL writing JOIN unqualified implies INNER JOIN. In other words the INNER in INNER JOIN is optional. INNER and CROSS are synonyms in MySQL. For clarity I write JOIN or INNER JOIN if I have a join condition and CROSS JOIN if I don't have a condition.

The allowed syntax for joins is described in the documentation.

Right now I'm thinking a stand-alone JOIN is nothing more than (identical to) using commas and WHERE clauses.

The effect is the same, but the history behind them is different. The comma syntax is from the ANSI-89 standard. However there are a number of problems with this syntax so in the ANSI-92 standard the JOIN keyword was introduced.

I would strongly recommend that you always use JOIN syntax rather than the comma.

  • T1 JOIN T2 ON ... is more readable than T1, T2 WHERE ....
  • It is more maintainable because the table relationships and filters are clearly defined rather than mixed together.
  • The JOIN syntax is easier to convert to OUTER JOIN than the comma syntax.
  • Mixing the comma and JOIN syntax in the same statement can give curious errors due to the precedence rules.
  • It is less likely to accidentally create a cartesian product when using the JOIN syntax due to a forgotten join clause, because the join clauses are written next to the joins and it is easy to see if one is missing.
zzapper
  • 4,743
  • 5
  • 48
  • 45
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Sweet, thanks for clarifying up this simple question for me :) In the past I've always used commas+where clauses... but will be converting to using JOINs per your advice. Thanks – Quang Van Dec 11 '10 at 20:51
  • Hey Mark, what do you mean by mixes of JOIN and commas. Mix queries like this, SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) ? – Quang Van Dec 12 '10 at 00:00
  • 2
    @Quang: This for example will fail: `SELECT * FROM t1, t2 JOIN t3 ON t1.x=t3.y WHERE t1.a = t2.b` – Mark Byers Dec 12 '10 at 00:02
1

These are all equivalent, and also equal to, CROSS JOIN.

There are some differences between using comma and [INNER | CROSS] JOIN syntax, which might be important when joining more tables. Pretty much all you need to know is described here in the MySQL JOIN documentation.

Michael Currie
  • 13,721
  • 9
  • 42
  • 58
Mchl
  • 61,444
  • 9
  • 118
  • 120
  • 1
    ^--- answer to question is JOIN (standalone) basically is the same thing to INNER and comma+where clauses – Quang Van Dec 11 '10 at 23:58