LEFT JOIN (OUTER JOIN) vs INNER JOIN 中的条件 [英] Conditions in LEFT JOIN (OUTER JOIN) vs INNER JOIN

问题描述

SELECT A.COL1, B.COL1,C.COL1
FROM TABLEA A
LEFT JOIN TABLEB B ON A.COL1 = B.COL1
LEFT JOIN TABLEC C ON (
        C.COL3 IS NOT NULL
        AND (
              C.COL2 = 664
              AND A.COL1 = C.COL1
        )
)

关于SQL的技术性，LEFT JOIN TABLE C ON后面括号里的条件是什么意思?为什么需要这些?

In regards to technicalities of SQL, what does the condition written in parentheses after LEFT JOIN TABLE C ON mean? Why are those necessary?

推荐答案

内部联接(JOIN 或 INNER JOIN、CROSS JOIN 或逗号)首先执行 CROSS JOIN.(即返回所有可以通过附加左表中的一行和右表中的行来创建的行.)然后任何 ON 删除不满足其条件的行.OUTER JOIN 返回对应的 INNER JOIN plus 的行，对于左表 (LEFT) 或右表 (RIGHT) 或两个表 (FULL)，任何不匹配的行都用 NULL 扩展.在 FROM a WHERE 删除不满足其条件的行之后.

An inner join (JOIN or INNER JOIN, CROSS JOIN or comma) first does a CROSS JOIN. (Ie returns all rows that can be made by appending a row from its left table and a row from its right table.) Then any ON removes rows that don't meet its condition. An OUTER JOIN returns the rows of a corresponding INNER JOIN plus, for the left table (LEFT) or right table (RIGHT) or both tables (FULL), any unmatched rows extended with NULLs. After the FROM a WHERE removes rows that don't meet its condition.

如果条件为 ON，则从 FROM 中删除匹配的行.但是，如果该条件在 WHERE 中，则匹配的行以及通过稍后连接合并它们的任何行仍会被删除.因此，如果 FROM 只有内部联接，那么条件是在 ON 还是 WHERE 中都没有关系.

If a condition is in an ON then matching rows are removed in the FROM. But if that condition is instead in a WHERE then matching rows and any rows incorporating them via later joins still get removed. So if a FROM only has inner joins then it doesn't matter whether a condition is in an ON or a WHERE.

但是如果 FROM 有一个 OUTER JOIN ON 条件，那么不满足条件的交叉连接行将被删除并添加某些 NULL 扩展行，而将该条件移动到 WHERE 执行删除但不添加.

But if a FROM has an OUTER JOIN ON a condition then cross join rows not meeting the condition are removed and certain NULL-extended rows are added whereas moving that condition to a WHERE does the removal but not the addition.

语言没有必要为 INNER JOIN 设置 ON，因为不是 t1 INNER JOIN t2 ON 条件 可能涉及 (SELECT * FROM t1 INNER JOIN t2 WHERE 条件)代码>代替.

It's not necessary for the language to have ON for INNER JOIN since instead of t1 INNER JOIN t2 ON condition one could involve (SELECT * FROM t1 INNER JOIN t2 WHERE condition) instead.

从上面可以计算出以下内容:对于任何最后一个 OUTER JOIN 之后的 INNER JOIN 序列(包括没有 OUTER JOIN 时)，可以在它们的 ON 和 WHERE 之间自由移动条件.但不适用于任何最后一个 OUTER JOIN 或之前的 ON，因为它们会影响其输入，从而影响输出的 NULL 行.如果将这种条件从 ON 移到 WHERE，就没有理由期待相同的结果.

From the above you can work out the following: For a sequence of INNER JOINS after any last OUTER JOIN (including when there are no OUTER JOINs) one can freely move conditions between their ONs and a WHERE. But not for ONs of or before any last OUTER JOIN because they can affect its inputs and so affect what NULLed rows are output. There's just no reason to expect the same result if such a condition were moved from the ON to a WHERE.

对于您的代码:可能查询旨在将 A.COL1 作为具有关联 A、B 和 C 信息的 id 返回，但不包含 B 信息的那些(B & C 信息为 NULL)和那些有但没有 C 信息或有但没有非 NULL C.COL3 或有但没有 C.COL2=664 的那些但包括(C 信息为 NULL).

For your code: Likely the query is designed to return A.COL1 as an id with associated A, B and C info, with those that don't have B info nevertheless included (with B & C info NULLed) and those that do but don't have C info or do but don't have non-NULL C.COL3 or do but don't have C.COL2=664 nevertheless included (with C info NULLed).

这篇关于LEFT JOIN (OUTER JOIN) vs INNER JOIN 中的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！