LEFT JOIN WHERE not returning results

There are two tables with columns and I’m doing a basic LEFT JOIN:

Case 1

SELECT g_users.id, g_users.username, f_list.user_id
FROM g_users

LEFT JOIN f_list 
    ON f_list.friend_id = g_users.id 

WHERE g_users.username IN ('meme', 'john', 'doe')
    AND g_users.id != 4

Result:

All good!

Case 2, applied on same records

SELECT g_users.id, g_users.username, f_list.user_id
FROM g_users

LEFT JOIN f_list ON 
    f_list.friend_id = g_users.id 

WHERE g_users.username IN ('meme', 'john', 'doe')
    AND g_users.id != 4
    AND f_list.user_id != 4

Result:

I was expecting to return doe’s records but it returned nothing. What am I missing?

Answer

Ah, the wonderful world of NULL handling.

The problem arises when doing a comparison in your WHERE clause with the table you’re left joining to. You have that f_list.user_id != 4. For the rows that don’t have a match with your left join, this expression is evaluating if null != 4, which is false because it’s a null value.

You have 2 unique matched values, null and 4, of which both get excluded in the above statement, which leaves you with 0 rows as a result.

To get your desired outcome, you can wrap f_list.user_id in a null handling function, such as COALESCE.