MySQL WHERE x OR IS NULL not working

Question

select * from inventory.home_picture_types hpt
left join inventory.home_pictures hp on hp.home_picture_type_id = hpt.id
where hp.id IS NULL;

This query works on vagrant/homestead, and returns the home_picture_types with NULL home_picture values (correct, I'm just trying to get the home_picture_types).

However, when I run this query on CentOS Maria DB, it returns no results at all. It's as though the LEFT JOIN is an INNER JOIN.

I have written this query 700 different ways, and nothing is helping. I checked the sql_mode and it's set to '' on both databases.... Any ideas why it works on one and not the other??

NULL is never equal to anything including itself. Your SQL doesn't make sense. — user207421, Jun 30 '16 at 02:13
`select * from inventory.home_picture_types hpt left join inventory.home_pictures hp on hp.home_picture_type_id = hpt.id where hp.id='';` try this. — Hamza Zafeer, Jun 30 '16 at 02:16
It should return the rows that don't have a matching hpt.id and hp.home_picture_type, right? I'm trying to get where they don't match, which would just return something like: http://tinypic.com/r/243pzti/9 — bi4nchi, Jun 30 '16 at 02:17
Your suggestion returns nothing just as = NULL does, but I didn't try that at first.. — bi4nchi, Jun 30 '16 at 02:18
You sure the database has the same data? I don't see anything wrong with your query... — sgeddes, Jun 30 '16 at 02:23
Ya as far as I can tell. I truncated the tables, and copied/pasted the data into the table again. (just the 5 rows in the image). Do you think that the table could be corrupted or something? I just created it like 30 mins ago. — bi4nchi, Jun 30 '16 at 02:26
Well I have a link where 9 man hours were burned on this site, and in the end, it was the wrong database. So, recheck stuff cuz you are talking about different boxes and inter-db joins — Drew, Jun 30 '16 at 02:31
Ah damnit. The data was not the same. The home_pictures table was empty in homestead. SOOO, would have any suggestions on a query to retrieve the results I'd like? I just need all the home_picture_types no matter what, and if there no home_picture that's fine. — bi4nchi, Jun 30 '16 at 02:33

score 0 · Answer 1 · answered Jul 05 '16 at 02:42

All the types: select DISTINCT home_picture_type_id from inventory.home_picture_types.

Types for which there is no pic: select home_picture_type_id from inventory.home_picture_types AS hpt WHERE NOT EXISTS( SELECT * FROM inventory.home_pictures WHERE home_picture_type_id = hpt.id ).

MySQL WHERE x OR IS NULL not working

1 Answers1