dmr dmr - 4 months ago 21
SQL Question

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

What is the difference between

select * from A, B


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?


They return the same results because they are semantically identical. This:

select * 
  from A, B (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 =*