SQL 连接格式 - 嵌套内部连接 [英] SQL join format - nested inner joins

问题描述

我在重构的旧系统中有以下 SQL 语句.就这个问题而言，这是一个缩写视图，暂时只返回 count(*).

I have the following SQL statement in a legacy system I'm refactoring. It is an abbreviated view for the purposes of this question, just returning count(*) for the time being.

SELECT COUNT(*)
FROM Table1 
    INNER JOIN Table2 
        INNER JOIN Table3 ON Table2.Key = Table3.Key AND Table2.Key2 = Table3.Key2 
    ON Table1.DifferentKey = Table3.DifferentKey

它正在生成大量记录并杀死系统，但有人可以解释一下语法吗?这可以用任何其他方式表达吗?

It is generating a very large number of records and killing the system, but could someone please explain the syntax? And can this be expressed in any other way?

• 表 1 包含 419 行
• 表 2 包含 3374 行
• Table3 包含 28182 行

建议重新格式化

SELECT COUNT(*)
FROM Table1 
    INNER JOIN Table3
          ON Table1.DifferentKey = Table3.DifferentKey
    INNER JOIN Table2 
          ON Table2.Key = Table3.Key AND Table2.Key2 = Table3.Key2

推荐答案

为了可读性，我重新构造了查询...从最顶层开始，表 1 与表 3 相关联，然后表 3 与表 2 相关联.如果您遵循关系链，则更容易遵循.

For readability, I restructured the query... starting with the apparent top-most level being Table1, which then ties to Table3, and then table3 ties to table2. Much easier to follow if you follow the chain of relationships.

现在回答你的问题.作为笛卡尔积的结果，您得到了大量计数.对于在 Table3 中匹配的 Table1 中的每条记录，您将有 X * Y.然后，对于 table3 和 Table2 之间的每个匹配都会产生相同的影响...... Y * Z ... 所以你的结果只有表 1 中的一个可能的 ID可以有 X * Y * Z 记录.

Now, to answer your question. You are getting a large count as the result of a Cartesian product. For each record in Table1 that matches in Table3 you will have X * Y. Then, for each match between table3 and Table2 will have the same impact... Y * Z... So your result for just one possible ID in table 1 can have X * Y * Z records.

这是基于不知道您的表格的规范化或内容如何...如果键是 PRIMARY 键...

This is based on not knowing how the normalization or content is for your tables... if the key is a PRIMARY key or not..

Ex:
Table 1       
DiffKey    Other Val
1          X
1          Y
1          Z

Table 3
DiffKey   Key    Key2  Tbl3 Other
1         2      6     V
1         2      6     X
1         2      6     Y
1         2      6     Z

Table 2
Key    Key2   Other Val
2      6      a
2      6      b
2      6      c
2      6      d
2      6      e

因此，表 1 连接到表 3 将导致(在这种情况下)有 12 条记录(每 1 条记录与每条记录 3 条记录).然后，所有这些再次乘以表 2 中每个匹配的记录(5 条记录)......总共 60 ( 3 tbl1 * 4 tbl3 * 5 tbl2 )count 将被返回.

So, Table 1 joining to Table 3 will result (in this scenario) with 12 records (each in 1 joined with each in 3). Then, all that again times each matched record in table 2 (5 records)... total of 60 ( 3 tbl1 * 4 tbl3 * 5 tbl2 )count would be returned.

那么，现在，根据您的 1000 条记录进行扩展，您会看到一个混乱的结构如何扼杀母牛(可以这么说)并扼杀性能.

So, now, take that and expand based on your 1000's of records and you see how a messed-up structure could choke a cow (so-to-speak) and kill performance.

SELECT
      COUNT(*)
   FROM
      Table1 
         INNER JOIN Table3
            ON Table1.DifferentKey = Table3.DifferentKey
            INNER JOIN Table2
               ON Table3.Key =Table2.Key
               AND Table3.Key2 = Table2.Key2 

这篇关于SQL 连接格式 - 嵌套内部连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！