什么是“表A左外部连接表B ON TRUE"? [英] What means "table A left outer join table B ON TRUE"?

问题描述

我知道表联接中使用了条件.但是我遇到了一种特殊情况，SQL代码写为"Table A join table B ON TRUE"

I know conditions are used in table joining. But I met a specific situation and the SQL codes writes like "Table A join table B ON TRUE"

根据"ON TRUE"条件会发生什么?只是完全交叉联接而没有任何条件选择吗?

What will happen based on the "ON TRUE" condition? Is that just a total cross join without any condition selection?

实际上，原始表达是这样的:

Actually, the original expression is like:

Table A LEFT outer join table B on TRUE

比方说，A有m行，B有n行. 左外部联接"与按真"之间是否有冲突?因为似乎正确"会导致交叉连接.

Let's say A has m rows and B has n rows. Is there any conflict between "left outer join" and "on true"? Because it seems "on true" results a cross join.

根据我的猜测，结果将是m * n行.因此，它无需编写左外部联接"，只需一个联接"将给出相同的输出，对吧?

From what I guess, the result will be m*n rows. So, it has no need to write "left outer join", just a "join" will give the same output, right?

推荐答案

是.这和CROSS JOIN一样.

在MySQL中，我们可以省略[可选] CROSS关键字.我们还可以省略ON子句.

In MySQL, we can omit the [optional] CROSS keyword. We can also omit the ON clause.

ON子句中的条件被评估为布尔值，因此我们也可以编写类似于ON 1=1的内容.

The condition in the ON clause is evaluated as a boolean, so we could also jave written something like ON 1=1.

更新:

(该问题已被编辑，以添加关于a LEFT [OUTER] JOIN b的另一个问题，该问题与原始构造不同:a JOIN b)

(The question was edited, to add another question about a LEFT [OUTER] JOIN b which is different than the original construct: a JOIN b)

"LEFT [OUTER] JOIN"稍有不同，因为即使在右侧的表中找不到匹配的行，也将返回左侧表中的行.

The "LEFT [OUTER] JOIN" is slightly different, in that rows from the table on the left side will be returned even when there are no matching rows found in the table on the right side.

如前所述，表 a (包含 m 行)和表 b 之间包含 n CROSS JOIN >行，如果没有其他谓词，将产生 m x n 行的结果集.

As noted, a CROSS JOIN between tables a (containing m rows) and table b containing n rows, absent any other predicates, will produce a resultset of m x n rows.

在特殊情况下，LEFT [OUTER] JOIN将产生不同的结果集，其中表 b 包含0行.

The LEFT [OUTER] JOIN will produce a different resultset in the special case where table b contains 0 rows.

CREATE TABLE a (i INT);
CREATE TABLE b (i INT);
INSERT INTO a VALUES (1),(2),(3);

SELECT a.i, b.i FROM a LEFT JOIN b ON TRUE ;

请注意，即使表b包含0行，LEFT JOIN也将从表a返回行(总共 m 行).

Note that the LEFT JOIN will returns rows from table a (a total of m rows) even when table b contains 0 rows.

这篇关于什么是“表A左外部连接表B ON TRUE"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！