How to use mysql JOIN without ON condition?

小开

最佳答案

MySQL文件涵盖了这个主题。

Here is a synopsis. When using join or inner join, the on condition is optional. This is different from the ANSI standard and different from almost any other database. The effect is a cross join. Similarly, you can use an on clause with cross join, which also differs from standard SQL.

A cross join creates a Cartesian product -- that is, every possible combination of 1 row from the first table and 1 row from the second. The cross join for a table with three rows ('a', 'b', and 'c') and a table with four rows (say 1, 2, 3, 4) would have 12 rows.

实际上，如果您想进行交叉连接，那么可以使用 cross join:

from A cross join B

比:

from A, B

以及:

from A join B -- with no on clause

on子句是右侧或左侧外部联接所必需的，因此讨论与它们无关。

小开

请参见 http://www.sitepoint.com/understanding-sql-joins-mysql-database/中的一些示例

可以使用“ USING”而不是查询中的“ ON”

SELECT * FROM table1 LEFT JOIN table2 USING (id);

小开

有几种方法可以实现交叉连接或笛卡儿积:

SELECT column_names FROM table1 CROSS JOIN table2;

SELECT column_names FROM table1, table2;

SELECT column_names FROM table1 JOIN table2;

在第三种情况下忽略 on条件会导致交叉连接。