sql - Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?

Question

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)?

---

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!!

(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)

Answer 1

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 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

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.)

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.)