Does an INNER JOIN Override a Prior OUTER JOIN's Results?

Combined use of INNER JOIN and OUTER JOIN

When executing multi-table join queries, the order of INNER JOIN and OUTER JOIN is crucial. Many people mistakenly believe that using an INNER JOIN after an OUTER JOIN will cancel out the effect of the OUTER JOIN, but this is not entirely true.

The role of INNER JOIN

INNER JOIN removes rows from the result set that do not have matching rows in the joined table. If there are no NULL values ​​in matching columns (for example, primary key and foreign key pairs), these unmatched rows are eliminated anyway.

Potential Problems

The effect of an OUTER JOIN will be offset by a subsequent INNER JOIN only if the ON clause of the INNER JOIN specifies required rows in the OUTER JOIN that may be excluded. For example, in the following query:

<code class="language-sql">SELECT *
FROM person
LEFT JOIN address
ON person.address_id = address.id
INNER JOIN city
ON address.city_id = city.id</code>

The second ON clause requires that address.city_id is not NULL, which may negate the effect of the LEFT JOIN. To resolve this issue, the second JOIN should be changed to a LEFT OUTER JOIN.

Best Practices

Although it does not change the results, it is generally recommended to put INNER JOIN before OUTER JOIN in the query. This helps improve readability and ensures that necessary restrictions are applied before optional restrictions are applied. Also, avoid using RIGHT OUTER JOIN when possible and use LEFT OUTER JOIN instead, which improves clarity and ease of implementation.

The above is the detailed content of Does an INNER JOIN Override a Prior OUTER JOIN's Results?. For more information, please follow other related articles on the PHP Chinese website!