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?
views:
125answers:
4But if you *do* need to cross join them, please use the latter :-) It's quite silly that cross join got that shortcut notation.
pst
2010-10-12 20:10:21
I think the "shortcut notation" predates the cross join notation.
Otávio Décio
2010-10-12 20:11:52
You're correct--`CROSS JOIN` became ANSI standard in SQL:1992, vs the ANSI SQL:1989 syntax using commas.
OMG Ponies
2010-10-12 20:45:35
+2
A:
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.
Mark Byers
2010-10-12 20:11:06
+8
A:
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 =*
OMG Ponies
2010-10-12 20:11:34
+1, but FWIW they're not syntactically identical -- they're *semantically* identical.
Bill Karwin
2010-10-12 20:47:11
+1
A:
These are the examples of implicit and explicit cross joins. See http://en.wikipedia.org/wiki/Join_%28SQL%29#Cross_join.
eugene y
2010-10-12 20:18:12
I disagree that they are explicit/implicit--both are ANSI standard, and the ANSI-89 format is considered deprecated.
OMG Ponies
2010-10-12 20:23:09