是否在任何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，FULL)之前?

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编辑时都将被删除，这仅仅是因为没有任何内容与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的列联接任何两个表，因此，当INNER JOIN -ing时，我不会评论NULL值是否与NULL值匹配桌子.至少，这是非常罕见的，我想)

(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子句要求存在应为可选的行，则随后的内部联接只会实质上使"外部联接无效.在这种情况下，对连接重新排序要么无效，要么无济于事.相反，唯一的解决方法是将内部联接更改为适当的外部联接.

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.

例如，这很好用:

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

等效于在内部联接(第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屋！