I am wondering whether CROSS JOIN
can be safely replaced with INNER JOIN
in any query when it is found.
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?
An appendix to this question would be:
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 )
?
Thank you.
In all modern databases all these constructs are optimized to the same plan.
Some databases (like SQL Server
) require an ON
condition after the INNER JOIN
, so your third query just won't parse there.
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
won't parse, while this one:
SELECT *
FROM s2
CROSS JOIN
s3
JOIN s1
ON s1.id IN (s2.id, s3.id)
will.