35

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.

Benoit
  • 76,634
  • 23
  • 210
  • 236

2 Answers2

30

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.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • Thank you for the answer. Is the `ON` condition mandatory according to the standard for `INNER JOIN`s? – Benoit Apr 21 '11 at 11:15
  • @Benoit: according to `SQL-92`, it is not. I don't have a copy of `2003` or `2008` handy, though. All databases except `MySQL` require it. – Quassnoi Apr 21 '11 at 11:22
  • @Benoit: I meant "all databases of the big four", sorry :) – Quassnoi Apr 21 '11 at 11:45
  • @Quassnoi: does this mean Oracle, MySQL, PostgreSQL and SQL Server? – Benoit Apr 21 '11 at 11:49
  • In the second version ofthe query, I think you mean "FROM s2". It won't parse as is. Arguably worth a downvote (It's not mine), but it's an easy fix. – mc0e Sep 02 '13 at 06:43
3

A raw cross join is one that has no where clause meaning that one record is produced for every combination of the left and right tables being joined with nulls inserted where there is no left or right side data.

If you add a where clause to a cross join this makes it equivalent to an inner join as the where clause does the same thing as the ON in the inner join.

However, inner joins are generally nicer to use as this separates the ON condition away from the rest of your where clauses making it easier to understand.

Chris Snowden
  • 4,982
  • 1
  • 25
  • 34
  • 4
    In your first paragraph you are confusing CROSS and OUTER joins. Cross-joining with an empty table issues an empty result. – Benoit Apr 21 '11 at 12:07