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
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
I was expecting to return doe’s records but it returned nothing. What am I missing?
Advertisement
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.