-1

Recently, I came across an SQL query which looked like this:

SELECT * FROM A, B WHERE A.NUM = B.NUM

To me, it seems as if this will return exactly the same as an INNER JOIN: 

SELECT * FROM A INNER JOIN B ON A.NUM = B.NUM

Is there any sane reason why anyone would use a CROSS JOIN here? Edit: it seems as if most SQL applications will automatically use a INNER JOIN here.

The database is HSQLDB

user3726374
  • 583
  • 1
  • 4
  • 24
  • 5
    The first one isn't a `CROSS JOIN` it is an older form of an `INNER JOIN` with the join condition in the `WHERE`. The queries are the same. – Taryn Sep 22 '14 at 14:35
  • Could you please specify what RDBMS you're using so we don't have to guess? – Aaron Bertrand Sep 22 '14 at 14:43
  • No, there is no reason why any sane person would use a CROSS JOIN here. CROSS JOIN implies that you want a Cartesian product - while it is possible to coerce it to behave like an INNER JOIN with additional filters, I don't see the point. When you want an INNER JOIN, use INNER JOIN; when you want a CROSS JOIN, use CROSS JOIN. Regardless of what the open source documentation says or how badly MySQL wants to deviate from the SQL standard. I can ride my tricycle on a lot of highways where non-motorized vehicles aren't explicitly forbidden, but that doesn't mean it is sane or logical to do so. – Aaron Bertrand Sep 22 '14 at 14:52

2 Answers2

4

The older syntax is a SQL antipattern. It should be replaced with an inner join anytime you see it. Part of why it is an antipattern is because it is impoosible to tell if a cross join was intended or not if the where clasues is ommitted. This causes many accidental cross joins espcially in complex queries. Further, in some databases (espcially Sql server) the implict outer joins do not work correctly and so people try to combine explicit and implict joins and get bad results without even realizing it. All in all it is a poor practice to even consider using an implict join.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • 1
    There are good reasons that the `JOIN` keyword is preferred over the old-school comma syntax for a join operation. One of the biggest reasons is that with "wrong" SQL written using the comma syntax, it's harder to pick out what is wrong. With an appropriate usage of the JOIN keyword and join predicates in the ON clause, it makes it easier to spot what's wrong with a SQL statement. Joel didn't specifically address SQL in his decade old blog post: [Making wrong code look wrong](http://www.joelonsoftware.com/articles/Wrong.html), but I think the same principles apply. – spencer7593 Sep 22 '14 at 15:49
0

Yes, your both statements will return the same result. Which one is to be used is a matter of taste. Every sane database system will use a join for both if possible, no sane optimizer will really use a cross product in the first case.

But note that your first syntax is not a cross join. It is just an implicit notation for a join which does not specify which kind of join to use. Instead, the optimizer must check the WHERE clauses to determine whether to use an inner join or a cross join: If an applicable join condition is found in the WHERE clause, this will result in an inner join. If no such clause is found it will result in a cross join. Since your first example specifies an applicable join condition (WHERE A.NUM = B.NUM) this results in an INNER JOIN and thus exactly equivalent to your second case.

gexicide
  • 38,535
  • 21
  • 92
  • 152
  • +1. The only "sane" reason that someone would replace the keyword "INNER" with "CROSS" in the OP example is that they were using **MySQL**, and they are aware that **in MySQL** "JOIN", "INNER JOIN" and "CROSS JOIN" are synonymous. – spencer7593 Sep 22 '14 at 14:57
  • @spencer7593 I don't understand why a sane person would replace INNER with CROSS even in the rare case where they are the same. They *can* be used for different purposes, so why not keep them separate instead of advocating that you interchange them? The fact that Yosemite Sam is probably responsible for the glitch in MySQL that allows INNER JOIN without an ON clause is no excuse. And still, we have ***NO IDEA*** if the OP is even using MySQL. – Aaron Bertrand Sep 22 '14 at 14:59
  • I must agree that the MySQL semantics of `CROSS JOIN` are quite strange. Even if it is possible in MySQL, I would refrain from writing `CROSS JOIN` for a join that is not meant to be a real cross product. This would obfuscate the meaning of the query and would make readers that are unfamiliar with MySQL go nuts. – gexicide Sep 22 '14 at 15:04
  • 2
    No it is not a matter of taste. Implicit joins are known SQL antipattern. They often cause problems that you don't get with explicit joins such as accidental cross joins. Implict joins were replaced more than 20 years ago, it is time to stop using them entirely. – HLGEM Sep 22 '14 at 15:12