如何在没有 ON 条件的情况下使用 mysql JOIN?

2021-11-20 00:00:00 join left-join mysql inner-join right-join

是否可以在没有 ON 语句的情况下编写连接查询?以及这些连接有何不同LEFT JOIN, RIGHT JOIN 的工作原理.

Is it possible to write join query without ON statement? and how do these joins differ LEFT JOIN, RIGHT JOIN works.

推荐答案

MySQL 文档 涵盖此主题.

MySQL documentation covers this topic.

这是一个概要.当使用joininner join 时,on 条件是可选的.这不同于 ANSI 标准,也不同于几乎任何其他数据库.效果是cross join.同样,您可以将 on 子句与 cross join 一起使用,这也不同于标准 SQL.

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.

交叉联接创建笛卡尔积——即第一个表中的 1 行和第二个表中的 1 行的所有可能组合.三行('a'、'b' 和 'c')表和四行表(比如 1、2、3、4)的交叉联接将有 12 行.

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:

In practice, if you want to do a cross join, then use cross join:

from A cross join B

远胜于:

from A, B

和:

from A join B -- with no on clause

右外连接或左外连接需要 on 子句,因此讨论与它们无关.

The on clause is required for a right or left outer join, so the discussion is not relevant for them.

如果您需要了解不同类型的连接,那么您需要对关系数据库进行一些研究.Stackoverflow 不适合进行这种级别的讨论.

If you need to understand the different types of joins, then you need to do some studying on relational databases. Stackoverflow is not an appropriate place for that level of discussion.

相关文章