Menu
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.
604
CROSS JOIN is the full cartesian product of the two sides of a JOIN. INNER JOIN is a reduction of the cartesian product—we specify a predicate and get a result where the predicate matches.
The most important and frequently used of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN. The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate.
A CROSS JOIN is a JOIN operation that produces the Cartesian product of two tables. Unlike other JOIN operators, it does not let you specify a join clause.
We can use 'cross join' without on condition. Cross join gives the result in cartesian product form. For instance, if in one table there are 3 records and another table has 2 records, then the first record will match with all the second table records. Then, the same process will be repeated for second record and so on.
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.
167
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
