左连接或从多个表中选择使用逗号(，)[复制]

This question already has an answer here:

这个问题已经有了答案:

SQL left join vs multiple tables on FROM line? 12 answers
SQL左连接与多个表联机?12个答案

I'm curious as to why we need to use LEFT JOIN since we can use commas to select multiple tables.

我很好奇为什么我们需要使用左连接，因为我们可以使用逗号来选择多个表。

What are the differences between LEFT JOIN and using commas to select multiple tables.

在选择多个表时，左连接和使用逗号之间的区别是什么?

Which one is faster?

哪个更快呢?

Here is my code:

这是我的代码:

   SELECT mw.*, 
          nvs.* 
     FROM mst_words mw 
LEFT JOIN (SELECT no as nonvs, 
                  owner, 
                  owner_no, 
                  vocab_no, 
                  correct 
             FROM vocab_stats 
            WHERE owner = 1111) AS nvs ON mw.no = nvs.vocab_no 
    WHERE (nvs.correct > 0 ) 
      AND mw.level = 1

...and:

SELECT * 
  FROM vocab_stats vs, 
       mst_words mw 
 WHERE mw.no = vs.vocab_no 
   AND vs.correct > 0 
   AND mw.level = 1 
   AND vs.owner = 1111

3 个解决方案

#1

First of all, to be completely equivalent, the first query should have been written

首先，要完全等效，第一个查询应该已经写好了。

   SELECT mw.*, 
          nvs.* 
     FROM mst_words mw 
LEFT JOIN (SELECT *
             FROM vocab_stats 
            WHERE owner = 1111) AS nvs ON mw.no = nvs.vocab_no 
    WHERE (nvs.correct > 0 ) 
      AND mw.level = 1

So that mw.* and nvs.* together produce the same set as the 2nd query's singular *. The query as you have written can use an INNER JOIN, since it includes a filter on nvs.correct.

mw。*和神。*一起产生与第二个查询的单数*相同的集合*。您所编写的查询可以使用内部连接，因为它包含一个nvs的过滤器。

The general form

的一般形式

TABLEA LEFT JOIN TABLEB ON <CONDITION>

attempts to find TableB records based on the condition. If the fails, the results from TABLEA are kept, with all the columns from TableB set to NULL. In contrast

试图根据条件查找表b记录。如果失败，则保留TABLEA的结果，所有列从TableB设置为NULL。与此形成鲜明对比的是

TABLEA INNER JOIN TABLEB ON <CONDITION>

also attempts to find TableB records based on the condition. However, when fails, the particular record from TableA is removed from the output result set.

还试图根据条件查找表b记录。但是，当失败时，TableA中的特定记录将从输出结果集中删除。

The ANSI standard for CROSS JOIN produces a Cartesian product between the two tables.

交叉联接的ANSI标准在这两个表之间产生了一个笛卡尔积。

TABLEA CROSS JOIN TABLEB
  -- # or in older syntax, simply using commas
TABLEA, TABLEB

The intention of the syntax is that EACH row in TABLEA is joined to EACH row in TABLEB. So 4 rows in A and 3 rows in B produces 12 rows of output. When paired with conditions in the WHERE clause, it sometimes produces the same behaviour of the INNER JOIN, since they express the same thing (condition between A and B => keep or not). However, it is a lot clearer when reading as to the intention when you use INNER JOIN instead of commas.

语法的意图是，TABLEA中的每一行都与TABLEB中的每一行连接在一起。在A中有4行，在B中3行产生12行输出。当与WHERE子句中的条件匹配时，它有时会产生内部连接的相同行为，因为它们表达的是相同的情况(A和B =>之间的条件)。然而，当您使用内部连接而不是逗号时，阅读意图会更加清晰。

Performance-wise, most DBMS will process a LEFT join faster than an INNER JOIN. The comma notation can cause database systems to misinterpret the intention and produce a bad query plan - so another plus for SQL92 notation.

在性能方面，大多数DBMS将会比内部连接更快地处理一个左连接。逗号表示法可以导致数据库系统错误地解释意图，并产生一个糟糕的查询计划——因此，SQL92表示法的另一个加号。

Why do we need LEFT JOIN? If the explanation of LEFT JOIN above is still not enough (keep records in A without matches in B), then consider that to achieve the same, you would need a complex UNION between two sets using the old comma-notation to achieve the same effect. But as previously stated, this doesn't apply to your example, which is really an INNER JOIN hiding behind a LEFT JOIN.

为什么我们需要左连接?如果对左连接的解释仍然不够(在B中保留没有匹配的记录)，那么考虑到实现相同的结果，您需要使用旧的逗号符号来实现相同的效果，需要在两个集合之间建立一个复杂的联合。但如前所述，这并不适用于您的示例，它实际上是隐藏在左侧连接后面的内部连接。

Notes:

注:

The RIGHT JOIN is the same as LEFT, except that it starts with TABLEB (right side) instead of A.
右边的连接和左边的是一样的，只不过它是从TABLEB(右边)而不是A开始的。
RIGHT and LEFT JOINS are both OUTER joins. The word OUTER is optional, i.e. it can be written as LEFT OUTER JOIN.
右和左连接都是外连接。“外”这个词是可选的，也就是说，它可以写成左外连接。
The third type of OUTER join is FULL OUTER join, but that is not discussed here.
第三种外部连接是完整的外部连接，但这里没有讨论。

#2

Separating the JOIN from the WHERE makes it easy to read, as the join logic cannot be confused with the WHERE conditions. It will also generally be faster as the server will not need to conduct two separate queries and combine the results.

将JOIN与WHERE进行分隔使其易于阅读，因为连接逻辑不能与WHERE条件相混淆。它通常也会更快，因为服务器不需要进行两个单独的查询并合并结果。

The two examples you've given are not really equivalent, as you have included a sub-query in the first example. This is a better example:

您给出的两个示例实际上并不等价，因为在第一个示例中包含了子查询。这是一个更好的例子:

SELECT vs.*, mw.*
FROM vocab_stats vs, mst_words mw
LEFT JOIN vocab_stats vs ON mw.no = vs.vocab_no
WHERE vs.correct > 0 
AND mw.level = 1
AND vs.owner = 1111

#3

-3

A Visual Explanation of SQL Joins

SQL连接的可视化解释。

#1