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

Viewed 13664

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?

8 Answers

Besides brevity (favoring ,) and consistency (favoring CROSS JOIN), the sole difference is precedence.

The comma is lower precedence than other joins.


For example, the explicit form of

SELECT *
FROM a
  CROSS JOIN b
  JOIN c ON a.id = c.id

is

SELECT *
FROM (
  a
  CROSS JOIN b
)
  INNER JOIN c ON a.id = c.id

which is valid.

Whereas the explicit form of

SELECT *
FROM a,
  b
  JOIN c ON a.id = c.id

is

SELECT *
FROM a
  CROSS JOIN (
    b
    INNER JOIN c ON a.id = c.id
  )

which is invalid (the join clause references inaccessible a).


In your example, there are only two tables, so the two queries are exactly equivalent.

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.

Related