Understanding the Interaction of INNER and OUTER JOINs in SQL

Complex SQL queries frequently employ multiple JOIN operations, combining different JOIN types. A key consideration is how the inclusion of NULL values, a characteristic of OUTER JOINs, is affected when an INNER JOIN is subsequently applied.

How INNER JOINs Can Affect OUTER JOIN Results

An INNER JOIN, by its nature, only returns rows where the join condition is met in both tables. If an INNER JOIN follows an OUTER JOIN, and the INNER JOIN's condition relies on columns that might be NULL (due to the OUTER JOIN), it effectively filters out rows where those columns are NULL. This negates the inclusion of NULL values that the OUTER JOIN initially provided.

Illustrative Example

Let's examine a scenario:

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

The LEFT JOIN ensures that all rows from the person table are included, even if there's no matching entry in address. However, the INNER JOIN with email then restricts the results to only those rows where a matching email_id exists in both person and email. Rows from person that had a matching address but a NULL email_id will be excluded.

A Problematic Scenario and its Solution

Consider this query:

SELECT *
FROM person
LEFT JOIN address ON person.address_id = address.id
INNER JOIN city ON address.city_id = city.id;

Here, the INNER JOIN with city assumes that address.city_id is never NULL. If some addresses lack a city_id, those rows (and associated person rows) will be omitted, unintentionally altering the results of the initial LEFT JOIN.

The correct approach is to use another LEFT JOIN instead of the INNER JOIN:

SELECT *
FROM person
LEFT JOIN address ON person.address_id = address.id
LEFT JOIN city ON address.city_id = city.id;

This preserves the inclusivity of the initial LEFT JOIN, correctly handling cases where address.city_id might be NULL. This ensures that all person records are included, regardless of whether they have an address or a city associated with them.