AND VS OR in Where Clause-CodePudding

I am trying to investigate why my where clause doesn't work correctly.

Query -

select * from <tablename>
where 
   (column1 <> '0' OR column1 IS NULL) 
OR (column1 NOT LIKE '000%' OR column1 IS NULL)
OR (column1 NOT LIKE 'XXX%' OR column1 IS NULL)

This will return all rows

If I change it to -

select * from <tablename>
where 
   (column1 <> '0' OR column1 IS NULL) 
AND (column1 NOT LIKE '000%' OR column1 IS NULL)
AND (column1 NOT LIKE 'XXX%' OR column1 IS NULL)

It will filter correctly. Maybe I am having an off day but can someone explain this to me?

CodePudding user response：

To explain the difference just consider following scenario.

You have a row where column1 = '0' so it looks at your query. You say where column1 <> '0' so this fails. But it goes to next in query which is where column1 IS NULL it asks is it null? No because it has a value. So two failed. Next where column1 NOT LIKE '000%' is it not like '000%'? Yes so it returns true and returns you that row. And same happen with other that you try to exclude.

But when using and it checks where column1 <> '0'? No so fails. Next is it null? No failed. So now it fails in all checks because all grouped conditions need to return true.

So in summary and needs all to return true. While or only one needs to return true.

Just minor improvement on your post '0' should be 0 as an int not as a 'string'.