CROSS JOIN是没有ON子句的INNER JOIN的同义词吗? [英] Is CROSS JOIN a synonym for INNER JOIN without ON clause?

问题描述

我想知道是否可以在任何查询中用INNER JOIN安全地替换CROSS JOIN.

I am wondering whether CROSS JOIN can be safely replaced with INNER JOIN in any query when it is found.

没有ON或USING的INNER JOIN与CROSS JOIN完全相同吗?如果是，是否仅发明了CROSS JOIN类型只是为了在查询中更好地表达意图?

Is an INNER JOIN without ON or USING exactly the same as CROSS JOIN? If yes, has the CROSS JOIN type been invented only to express intent better in a query?

此问题的附录为:

使用CROSS JOIN ... WHERE x，INNER JOIN ... ON ( x )或INNER JOIN ... WHERE ( x )时，现代和广泛使用的DBMS是否有所不同?

Can there be a difference using modern and widely used DBMSes when using CROSS JOIN ... WHERE x, INNER JOIN ... ON ( x ) or INNER JOIN ... WHERE ( x ) ?

谢谢.

推荐答案

在所有现代数据库中，所有这些构造都针对同一计划进行了优化.

In all modern databases all these constructs are optimized to the same plan.

某些数据库(如SQL Server)在INNER JOIN之后需要一个ON条件，因此您的第三个查询将不会在该位置进行解析.

Some databases (like SQL Server) require an ON condition after the INNER JOIN, so your third query just won't parse there.

表的可见性范围是按JOIN顺序，因此此查询:

Visibility scope of the tables is in the JOIN order, so this query:

SELECT  *
FROM    s1
JOIN    s2
ON      s1.id IN (s2.id, s3.id)
CROSS JOIN
        s3

不会解析，而这个:

SELECT  *
FROM    s2
CROSS JOIN
        s3
JOIN    s1
ON      s1.id IN (s2.id, s3.id)

会.

这篇关于CROSS JOIN是没有ON子句的INNER JOIN的同义词吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！