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