是否需要INNER JOIN和OUTER JOIN？ [英] Are INNER JOIN and OUTER JOIN necessary?
问题描述
我知道任何连接都可以用CROSS连接和WHERE子句完成。
I understand that any join can be done with a CROSS join and a WHERE clause.
我做了一些实验，看起来像是将相等谓词放在
I did some experiments, and it looks like placing the equality predicate inside the where clause or as the parameter of an inner join yields the same result with the same performance.
此外，使用内部连接不会保存任何键入，因为连接谓词必须仍然被指定。
Furthermore, using inner joins does not save any typing, since the join predicate must still be specified.
我猜这同样适用于各种外部联接。
I guess that the same is true for the various kinds of outer joins. Just specify that the values can be null or not null.
我可以只使用交叉连接吗？
Can I just go with only cross joins ?
推荐答案
不能使用CROSS连接和WHERE子句来完成任何连接。每个连接交叉，内部和外部都有自己的逻辑意义。
Not any join can be done with a CROSS join and a WHERE clause.Each of the join cross, inner and outer has it's own logical significance.
- 交叉连接只应用一个阶段 - 笛卡尔积。
- 外连接应用三个阶段：笛卡尔乘积，过滤器和添加外部行。
- A cross join applies only one phase—Cartesian Product.
- An inner join applies two phases—Cartesian Product and Filter.
- An outer join applies three phases—Cartesian Product, Filter, and Add Outer Rows.
包含OUTER JOIN子句的查询的混乱方面是是否在ON过滤器或WHERE过滤器中指定逻辑表达式。两者之间的主要区别是在添加外侧行之前应用ON ，而之后应用 WHERE 。通过ON过滤器从保留的表（由左外部或右外部指定）中的行的消除不是最终的，因为它将被添加回来;
A confusing aspect of queries containing an OUTER JOIN clause is whether to specify a logical expression in the ON filter or in the WHERE filter. The main difference between the two is that ON is applied before adding outer rows , while WHERE is applied afterwards. An elimination of a row from the preserved table (specified as left outer or right outer) by the ON filter is not final because it will be added back; an elimination of a row by the WHERE filter, by contrast, is final.
ON和WHERE子句之间的逻辑差异 使用内连接时，在具有内连接表运算符的ON子句中或在具有交叉连接表的Where子句中指定逻辑表达式无关紧要
This logical difference between the ON and WHERE clauses exists only when using an outer join. When you use an inner join, it doesn’t matter where you specify your logical expressions in ON clause with inner join table operator or in Where clause with cross join table operator.
希望这有助于！
这篇关于是否需要INNER JOIN和OUTER JOIN？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
