What is the difference between using a cross join and putting a comma between the two tables?

Question

What is the difference between

select * from A, B 

and

select * from A cross join B 

? They seem to return the same results.

Is the second version preferred over the first? Is the first version completely syntactically wrong?

Answer 1

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 =*

Answer 2

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.