比较MySQL Cross和Inner Joins

What are the potential pros and cons of each of these queries given different databases, configurations, etc? Is there ever a time when one would be more efficient than the other? Vice versa? Is there an even better way to do it? Can you explain why?

给定不同的数据库,配置等,每个查询的潜在优缺点是什么?有没有一个人比另一个人更有效率?反之亦然?有没有更好的方法呢?你能解释一下原因吗?

Query 1:

SELECT 
  *
FROM
  table_a, table_b, table_c
WHERE
  table_a.id = table_b.id AND
  table_a.id = table_c.id AND
  table_a.create_date > DATE('1998-01-01');

Query 2:

SELECT 
  *
FROM
  table_a 
INNER JOIN table_b ON
  table_a.id = table_b.id
INNER JOIN table_c ON
  table_a.id = table_c.id
WHERE
  table_a.create_date > DATE('1998-01-01');

4 个解决方案

#1

Same query, different revision of SQL spec. The query optimizer should come up with the same query plan for those.

相同的查询,不同的SQL规范修订版。查询优化器应该为这些查询计划提出相同的查询计划。

#2

Nope. I'm just sharing a large, overwhelmed database with some coworkers and am trying to come up with some ways to get more processor bang for our buck. I've been looking around online but haven't found a good explanation for some questions like this.

不。我只是与一些同事共享一个庞大的,不堪重负的数据库,我正试图想出一些方法来获得更多的处理器爆炸。我一直在网上寻找,但没有找到一些像这样的问题的好解释。

Sorry for sounding homework-y. I guess I spent too many years as a TA.

对不起听取作业-y。我想我花了太多年时间作为TA。

#3

Actually, I think query 2's more readable. Think about when you get to say 5,6, or 7 tables when you hit the where clause in query one. Following the joins could get messy.

实际上,我认为查询2更具可读性。想想当你点击查询一中的where子句时,你会说5,6或7个表。加入之后可能会变得混乱。

As for performance, I have no idea. I bet if you go to the MySQL website, there would be info there - probably examples of joins.

至于表现,我不知道。我敢打赌,如果你去MySQL网站,那里会有信息 - 可能是连接的例子。

Professionally, I've only worked on one project. But it was a big one, and they always followed query 2's format. This was using Microsoft SQL Server though.

从专业角度来说,我只参与过一个项目。但这是一个很大的问题,他们总是遵循查询2的格式。这是使用Microsoft SQL Server。

#4

-1

I agree, it's sounding a bit too much like Homework!

我同意,这听起来有点像家庭作业!

If it isn't homework then I guess the simplest answer is readability.

如果它不是家庭作业,那么我想最简单的答案是可读性。

As stated before, both queries will produce the same execution plan. If this is the case then the only thing you need to worry about it maintainability.

如前所述,两个查询都将生成相同的执行计划。如果是这种情况,那么您唯一需要担心的是可维护性。

#1