连接表或从多个表中选择

Which is better in between joining a table or selecting from multiple tables ?

在加入表格或从多个表格中选择之间哪个更好？

For instance, lets assume the following similar scenario:

例如，让我们假设以下类似的场景：

Using join:

使用join：

SELECT COALESCE(SUM(SALARY),0) FROM X
JOIN Y ON X.X_ID=Y.Y_X_ID

要么

By selecting from multiple tables

从多个表中选择

SELECT COALESCE(SUM(SALARY),0) FROM X, Y
WHERE X.X_ID=Y.Y_X_ID

3 个解决方案

#1

mainly join is used to retrieve data from multiple tables so in sql there are 3 types join are available

主要是join用于从多个表中检索数据，所以在sql中有3种类型的连接可用

Equi join-inner join outer join-left right full
Equi join-inner join outer join-left right full
Non equi join
非equi加入
Self join
自我加入
Cross join
交叉加入

#2

You should use the JOIN syntax for a lot of reasons which can be found here.

您应该使用JOIN语法有很多原因，可以在这里找到。

Moreover this syntax has the advantage to give some hints to the query optimizer (during the computation of weights, weights computed directly by the facts mentionned in this syntax are more favorably weighted than the others).

此外，这种语法的优点是可以为查询优化器提供一些提示（在计算权重期间，直接由此语法中提到的事实计算的权重比其他权重更有利）。

#3

Both are joins. The first is an explicit join and the second one is an implicit join and is a SQL antipattern.

两者都是连接。第一个是显式连接，第二个是隐式连接，是一个SQL反模式。

The second one is bad because it is easy to get an accidental cross join. It is also bad becasue when you want a cross join, it is not clear if your did want that or if you have an accidental one.

第二个是糟糕的，因为很容易发生偶然的交叉连接。这也是不好的，因为当你想要一个交叉连接时，不清楚你是否想要它或者你是否有意外的连接。

Further in the second style if you ned to convert to an outer join, you need to change all joins in the query or risk getting incorrect results. So the second style is harder to maintain.

进一步在第二种样式中，如果您需要转换为外部联接，则需要更改查询中的所有联接或冒险获取不正确的结果。所以第二种风格难以维持。

Explcit joins were institututed in the last century, why anyone is still using error-prone and hard to maintain implicit joins is beyond me.

Explcit连接在上个世纪是有效的，为什么任何人仍然使用容易出错且难以维护的隐式连接超出了我的范围。

#1