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

by

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

More Related Contents:

  1. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  2. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  3. How can I do a FULL OUTER JOIN in MySQL?
  4. INNER JOIN ON vs WHERE clause
  5. Difference between JOIN and INNER JOIN
  6. SQL Inner-join with 3 tables?
  7. Multiple INNER JOIN SQL ACCESS
  8. Oracle “(+)” Operator
  9. Difference between RIGHT & LEFT JOIN vs RIGHT & LEFT OUTER JOIN in SQL [duplicate]
  10. SQL SELECT from multiple tables
  11. INNER JOIN vs multiple table names in “FROM” [duplicate]
  12. What is the result of following SQL Query?
  13. Join vs. sub-query
  14. Can I use CASE statement in a JOIN condition?
  15. What is the difference between Left, Right, Outer and Inner Joins? [duplicate]
  16. Subqueries vs joins
  17. Difference between left join and right join in SQL Server [duplicate]
  18. Filter Table Before Applying Left Join
  19. SQL Join Differences
  20. Difference between natural join and inner join
  21. Are “from Table1 left join Table2” and “from Table2 right join Table1” interchangeable?
  22. Natural join in SQL Server
  23. Is a JOIN faster than a WHERE?
  24. Select only rows by join tables max value
  25. What is semi-join in database?
  26. How do I convert a “legacy” left outer join statement in Oracle?
  27. MySQL: GROUP_CONCAT with LEFT JOIN
  28. Joining multiple tables in SQL
  29. sql sum data from multiple tables
  30. SQL server join tables and pivot

Leave a Comment