Left Join Conundrum: Witching Hours When It Turns Into an Inner Join

In a database wizard's realm, performing complex data retrievals using left joins is a common practice. However, sometimes, the left join doesn't behave quite as expected.

Imagine the following query:

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';

Intriguingly, when the c.foobar condition is placed in the WHERE clause as above, the supposedly left join appears to transform into an inner join. Results are returned only if both a.foo and c.foobar criteria are met.

Why the metamorphosis? The key lies in the WHERE clause. When a value from the right side of a left join (right side referring to the table on the right side of the ON clause) is specified in the WHERE clause, all NULL values are discarded, effectively reducing the left join to an inner join. Simply put, if c.foobar is not present in tableThree, the query returns no rows.

To bypass this predicament, there are two options:

1. Amend the WHERE clause to consider both valid and NULL values for c.foobar:

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

2. Move the c.foobar condition into the join predicate:

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

Choosing between these solutions depends on the specific requirements of the query. However, understanding the underlying behavior of left joins in the presence of WHERE clauses is crucial for mastering data retrieval techniques.