Why Does a LEFT JOIN Become an INNER JOIN When Filtering on a Right-Side Column in the WHERE Clause?

Left Join Behavior Question: Transformation to Inner Join with WHERE Clause

Consider the following SQL statement:

SELECT
    a.foo
    b.bar
    c.foobar
FROM tableOne AS a
INNER JOIN tableTwo AS b ON a.pk = b.fk
LEFT JOIN tableThree AS c ON b.pk = c.fk
WHERE a.foo = 'something'
AND c.foobar = 'somethingelse'

In this scenario, the expectation is for a left join behavior. However, the behavior observed is that the left join appears to transform into an inner join based on the presence of the AND c.foobar = 'somethingelse' clause. Rows lacking a corresponding 'somethingelse' value in tableThree are not returned.

Understanding the Behavior

The關鍵點 lies in the interaction between the WHERE clause and the LEFT JOIN. When a non-NULL value is specified from the right side of a left join in a WHERE clause, the NULL values are eliminated, effectively turning the join into an inner join.

In this case, the AND c.foobar = 'somethingelse' clause filters out rows from tableThree where foobar is NULL. This means that only rows with matching non-NULL values in both tableTwo and tableThree are returned.

Solutions

To resolve this behavior and restore the intended left join, there are two options:

1. Modify WHERE Clause:

AND (c.foobar = 'somethingelse' OR c.foobar IS NULL)

This addition allows NULL values from tableThree to be included, preserving the left join behavior.

2. Move to Join Predicate:

LEFT JOIN tableThree AS c ON b.pk = c.fk
AND c.foobar = 'somethingelse'

By moving the c.foobar condition into the join predicate, the query explicitly specifies the desired filtering criteria within the join, ensuring that only matching rows are joined.

The above is the detailed content of Why Does a LEFT JOIN Become an INNER JOIN When Filtering on a Right-Side Column in the WHERE Clause?. For more information, please follow other related articles on the PHP Chinese website!