在任何 OUTER JOIN 之后使用 INNER JOIN 是否会本质上使 OUTER JOIN 的效果无效? [英] Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?

问题描述

换句话说，对于嵌套/多个 JOIN SQL 语句，是否应该始终首先使用 INNER JOIN (或者将其放在顶部)行或使用括号首先 INNER JOIN 两个表)并确保它在任何 OUTER JOIN 之前(LEFT, RIGHT，<代码>完整)?

In other words, for a nested/multiple JOIN SQL statement, is it safe to say that one should always use INNER JOIN first (either put it at the top line or by using parentheses to first INNER JOIN two tables) and make sure it precedes any OUTER JOIN (LEFT, RIGHT, FULL)?

我的理解是匹配的列(例如，主键列和外键列)通常没有 NULL 值.并且任何不匹配的行，包括 OUTER JOIN 结果中的 NULL 当被另一个表 INNER JOIN-ed 时都将被删除，因为没有将匹配 NULL!!

My understanding is that matching columns (e.g., Primary Key column and Foreign Key column) usually don't have NULL values. And any non-matching rows including NULL from an OUTER JOIN result would be removed when being INNER JOIN-ed by another table, simply because nothing would match a NULL!!

(顺便说一句，我从未使用都具有 NULL 的列连接任何两个表，因此，我不会评论 NULL 值是否与NULL 值，当 INNER JOIN-ing 表.至少，这将是非常罕见的，我猜)

(BTW, I never JOINed any two tables using columns that both have NULL, therefore, I would not comment on whether a NULL value would match a NULL value when INNER JOIN-ing tables. At least, this would be extremely rare, I guess)

推荐答案

如果内连接的 ON 子句需要 should-be-optional 行，则后续内连接只会实质上使"外连接无效"在场.在这种情况下，重新排序连接要么不起作用，要么无济于事；相反，唯一的解决方法是将内连接更改为适当的外连接.

A subsequent inner join will only "essentially invalidate" an outer join if the inner join's ON clause requires should-be-optional rows to be present. In such a case, reordering the join either won't work or won't help; rather, the only fix is to change the inner join to an appropriate outer join.

因此，例如，这可以正常工作:

So, for example, this works fine:

    SELECT *
      FROM person
 LEFT JOIN address
        ON person.address_id = address.id
INNER JOIN email
        ON person.email_id = email.id

and 等价于在内部连接(第 5-6 行)之后移动左外部连接(第 3-4 行)时得到的结果；而这并没有按预期工作:

and is equivalent to what you'd get if you moved the left outer join (lines 3–4) after the inner join (lines 5–6); whereas this does not work as intended:

    SELECT *
      FROM person
 LEFT JOIN address
        ON person.address_id = address.id
INNER JOIN city
        ON address.city_id = city.id

因为第二个 ON 子句只有在 address.city_id 为非空时才能满足.(在这种情况下，正确的解决方法是将内连接更改为左外连接.)

because the second ON clause can only be satisfied when address.city_id is non-null. (In this case the right fix is to change the inner join to a left outer join.)

话虽如此，我确实同意 Gordon Linoff 的观点，即通常最好将内连接放在左外连接之前；这是因为内部联接倾向于指示更多基本"限制，因此这种排序通常更具可读性.(我同意 Gordon Linoff 和 Shawn 的观点，即通常最好避免使用右外连接.)

That said, I do agree with Gordon Linoff that it's usually best to put your inner joins before your left outer joins; this is because inner joins tend to indicate more "essential" restrictions, so this ordering is usually more readable. (And I agree with both Gordon Linoff and Shawn that right outer joins are usually better avoided.)

这篇关于在任何 OUTER JOIN 之后使用 INNER JOIN 是否会本质上使 OUTER JOIN 的效果无效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！