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?

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

Solution 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.

Solution 3

They are the same and should (almost) never be used.

Solution 4

The first version was originally the only way to join two tables. But it has a number of problems so the JOIN keyword was added in the ANSI-92 standard. They give the same results but the second is more explicit and is to be preferred.

Solution 5

To add to the answers already given:

select * from A, B

This was the only way of joining prior to the 1992 SQL standard. So if you wanted an inner join, you'd have to use the WHERE clause for the criteria:

select * from A, B
where A.x = B.y;

One problem with this syntax was that there was no standard for outer joins. Another was that this gets unreadable with many tables and is hence prone to errors and less maintainable.

select * from A, B, C, D
where B.id = C.id_b
and C.id_d = D.id;

Here we have a cross join of A with B/C/D. On purpose or not? Maybe the programmer just forgot the and B.id = A.id_b (or whatever), or maybe this line was deleted by mistake, and maybe still it was really meant to be a cross join. Who could say?

Here is the same with explicit joins

select * 
from A
cross join B
inner join C on C.id_b = B.id
inner join D on D.id = C.id_d;

No doubt about the programmers intentions anymore.

The old comma-separated syntax was replaced for good reasons and should not be used anymore.

View more solutions

Comments

[JNK]

Cartesian products are almost never useful...

[dmr]

But in the rare cases that they are, it's nice to know the proper way to write them.

[NeedHack]

Rarely useful, but probably exist at least once in every major project.

[Arne]

There is actually a lot of uses for cross joins, e.g. to generate sample data or in geographic information processing (PostGIS). I use it a lot, e.g. to calculate distances (ST_Distance) between one point cross joining it to other points...

[Murta]

It’s a nice notion to unnest arrays and structs in BigQuery

[Admin]

But if you do need to cross join them, please use the latter :-) It's quite silly that cross join got that shortcut notation.

[Otávio Décio]

I think the "shortcut notation" predates the cross join notation.

[OMG Ponies]

I disagree that they are explicit/implicit--both are ANSI standard, and the ANSI-89 format is considered deprecated.

[OMG Ponies]

You're correct--CROSS JOIN became ANSI standard in SQL:1992, vs the ANSI SQL:1989 syntax using commas.

[Bill Karwin]

+1, but FWIW they're not syntactically identical -- they're semantically identical.

[OMG Ponies]

@Bill Karwin: Bah! All start with "s" :)

[Shnugo]

Found the same some minutes ago... Good to add this here!

[Me.Name]

Although this is an old post, purely for the record: there is a difference when another join or cross apply is used (stackoverflow.com/a/31441463/1431042 )