dmr dmr - 1 month ago 7
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


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

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

Comments