What is the difference between using a cross join and putting a comma between the two tables?
They return the same results because they are semantically identical. This:
select *
from A, B
...is (wince) ANSI-89 syntax. Without a WHERE clause to link the tables together, the result is a cartesian product. Which is exactly what alternative provides as well:
select *
from A
cross join B
...but the CROSS JOIN is ANSI-92 syntax.
About Performance
There's no performance difference between them.
Why Use ANSI-92?
The reason to use ANSI-92 syntax is for OUTER JOIN support (IE: LEFT, FULL, RIGHT)--ANSI-89 syntax doesn't have any, so many databases implemented their own (which doesn't port to any other databases). IE: Oracle's (+)
, SQL Server's =*
Stumbled upon this post from another SO question, but a big difference is the linkage cross join creates. For example using cross apply
or another join after B
on the first ('comma') variant, the cross apply or join would only refer to the table(s) after the dot. e.g, the following:
select * from A, B join C on C.SomeField = A.SomeField and C.SomeField = B.SomeField
would create an error:
The multi-part identifier "A.SomeField" could not be bound.
because the join on C only scopes to B, whereas the same with cross join...
select * from A cross join B join C on C.SomeField = A.SomeField and C.SomeField = B.SomeField
..is deemed ok. The same would apply if cross apply
is used. For example placing a cross apply on a function after B
, the function could only use fields of B, where the same query with cross join, could use fields from both A and B.
Of course, this also means the reverse can be used as well. If you want to add a join solely for one of the tables, you can achieve that by going 'comma' on the tables.
They are the same and should (almost) never be used.