Search code examples
sqlsql-servert-sqlselectcross-join

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?

Solution

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

    select * 
  from A, B

    ...is (wince) SQL-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 SQL-92 syntax.

    About Performance

    There's no performance difference between them.

    Why Use SQL-92?

    The reason to use SQL-92 syntax is for OUTER JOIN support (IE: LEFT, FULL, RIGHT)--SQL-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 =*