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

It is a good general rule to start with inner joins and then follow with left joins. right joins are almost never really needed, and full joins are a special case. This is basically how I write queries.

However, it does depend on what the join conditions are. So, although I think the rules above are reasonable and sufficient for writing almost any query, it is possible to write queries with inner joins following outer joins.

Answer 2

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

Answer 3

There is no notion of having to do things in a certain order. There are consequences of a particular choice of expression.

left join on returns inner join on rows plus unmatched left table rows extended by nulls. Similarly, right join on returns inner join on rows plus unmatched right table rows extended by nulls. Similarly, full join on returns inner join on rows plus unmatched left and right table rows extended by nulls. Always know what inner join on you want as part of an outer join on.

After a left/right/full join on, an inner join on, where or having needing some column(s) of the right/left/2 [sic] table(s) to be not null removes rows with introduced nulls, leaving only inner join on rows. It "turns OUTER JOIN into INNER JOIN". You are talking about that.

Similarly, after a full join on, needing some column(s) of only the right/left [sic] table to be not null "turns FULL JOIN into RIGHT/LEFT [sic] JOIN".

You shouldn't have to worry about this. Just focus on writing queries that return what you want. Your question is like asking, should I avoid dividing by zero since it's undefined or adding zero because it doesn't do anything? Why would you, since it doesn't do what you want? If you are writing wrong queries then find out what the operators do.

Is there any rule of thumb to construct SQL query from a human-readable description?

PS My characterization of outer joins & of when null-extended rows are dropped focuses on the associated inner join, on the on as a whole & on whetherleft and/or right table columns are null. That's because the simplest definitions of the operators involve only those things. Your choice of organization of parts is misleading & impeding you.

1. Any two tables can be joined on any condition. PK-FK equality is just a special case. (PKs & FKs are not needed to query. They do imply certain constraints on inputs & results though.) FK & unique columns & other columns can have nulls on input. (PK means unique not null.)
2. "matching columns" & "nothing would match a NULL" are confused because it's row pairs that match or don't, per the whole condition of an on.
3. "any non-matching rows including NULL from an OUTER JOIN result would be removed when being INNER JOIN"--No, it depends on the whole condition of an on or where.
4. "this would be extremely rare"--Doesn't matter. Either something can happen or it can't happen; either the query is wrong or it's right.