3

What is the difference?

SELECT a.name, b.name
FROM a, b;

SELECT a.name, b.name
FROM a
CROSS JOIN b;

If there is no difference then why do both exist?

GMB
  • 216,147
  • 25
  • 84
  • 135
AturSams
  • 7,568
  • 18
  • 64
  • 98
  • 1
    `why do both exist?` the old syntax exists due to backward compability reasons. They cannot remove the old syntax, because this would breake existing queries which use the old syntax. – krokodilko Dec 02 '18 at 20:12
  • @krokodilko, I am new to SQL so I wouldn't know it's old syntax. Also, when a method is old, it is usually deprecated. One more thing, this specific way (the old one) is still being thought in modern online classes. – AturSams Dec 03 '18 at 14:03

2 Answers2

6

The first with the comma is an old style from the previous century.

The second with the CROSS JOIN is in newer ANSI JOIN syntax.

And those 2 queries will indeed give the same results.

They both link every record of table "a" against every record of table "b".
So if table "a" has 10 rows, and table "b" has 100 rows.
Then the result would be 10 * 100 = 1000 records.

But why does that first outdated style still exists in some DBMS?
Mostly for backward compatibility reasons, so that some older SQL's don't suddenly break.

Most SQL specialists these days would frown upon someone who still uses that outdated old comma syntax. (although it's often forgiven for an intentional cartesian product)

A CROSS JOIN is a cartesian product JOIN that's lacking the ON clause that defines the relationship between the 2 tables.

In the ANSI JOIN syntax there are also the OUTER joins: LEFT JOIN, RIGHT JOIN, FULL JOIN

And the normal JOIN, aka the INNER JOIN.

enter image description here

But those normally require the ON clause, while a CROSS JOIN doesn't.

And example of a query using different JOIN types.

SELECT *
FROM jars
JOIN apples ON apples.jar_id = jars.id
LEFT JOIN peaches ON peaches.jar_id = jars.id
CROSS JOIN bananas AS bnns
RIGHT JOIN crates ON crates.id = jars.crate_id
FULL JOIN nuts ON nuts.jar_id = jars.id
WHERE jars.name = 'FruityMix'

The nice thing about the JOIN syntax is that the link criteria and the search criteria are separated.

While in the old comma style that difference would be harder to notice. Hence it's easier to forget a link criteria.

SELECT *
FROM crates, jars, apples, peaches, bananas, nuts
WHERE apples.jar_id = jars.id
  AND jars.name = 'NuttyFruitBomb'
  AND peaches.jar_id = jars.id(+)
  AND crates.id(+) = jar.crate_id;

Did you notice that the first query has 1 cartesian product join, but the second has 2? That's why the 2nd is rather nutty.

LukStorms
  • 28,916
  • 5
  • 31
  • 45
  • Such Venn diagrams are misleading & unclear. What are the elements of the sets? Not the rows of the "left table" & "right table" per your labels. Also SQL tables aren't even sets, they are bags. Just try to correctly define & label them & relate them to joins. See my comments & posts at [CROSS JOIN vs INNER JOIN](https://stackoverflow.com/a/25957600/3404097), [Venn Diagram for Natural Join](https://stackoverflow.com/a/55642928/3404097) & [INNER JOIN vs OUTER JOIN](https://stackoverflow.com/a/46091641/3404097) A correct presentation is so complicated that the diagrams don't clarify joins. – philipxy Feb 07 '22 at 12:52
  • @philipxy This old answer wasn't even trying to explain all the join types in depth. So it's just an extra, a teaser. The question was only about `CROSS JOIN`, old comma syntax versus join syntax. – LukStorms Feb 07 '22 at 13:06
  • Btw, [there's more than joins in SQL](https://stackoverflow.com/a/70198512/4003419) – LukStorms Feb 07 '22 at 14:51
  • I have no idea what your point is. Whatever it is, it doesn't affect my comment. The fact that the diagram isn't needed doesn't make it make sense. Nor does the fact that there are other operators. And if you read at the links in my comments you would see that I mention that Venn diagrams can be applied to SQL union/except. Although once again SQL tables are bags not sets & have nulls so again an actual correct use of Venn diagrams is so complicated that it does not help to educate about those operators. One needs to understand the operators to understand the diagrams. – philipxy Feb 07 '22 at 21:28
2

Both expressions perform a Cartesian product of the two given tables. They are hence equivalent.

Please note that from SQL style point of view, using JOIN has been the preferred syntax for a long time now.

GMB
  • 216,147
  • 25
  • 84
  • 135