sql - 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?

question from:https://stackoverflow.com/questions/3918570/what-is-the-difference-between-using-a-cross-join-and-putting-a-comma-between-th

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