左联接(外联接)vs内联接的条件 [英] 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都将删除不满足其条件的行.对于左表(LEFT)或右表(RIGHT)或两个表(FULL)，OUTER JOIN返回对应的INNER JOIN plus 的行，所有不匹配的行都扩展为NULL.在FROM 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启用，因为代替t1 INNER JOIN t2 ON condition的人可能会涉及(SELECT * FROM t1 INNER JOIN t2 WHERE condition).

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.

根据以上内容，您可以得出以下结论:对于任何最后一个外部联接之后的一系列内部联接(包括没有外部联接的情况)，人们可以在其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信息或没有C.COL3或没有C.COL2 = 664的人，但仍然包括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).

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