postgresql two NOT LIKE clauses returning wrong results

I have a table with 88 codes. I am writing a simple select statement that does not select the codes that start with 10 or 18

select distinct pcl_mun from exemptions.modiv_parcels_2015 
where (pcl_mun NOT LIKE '10%') or (pcl_mun NOT LIKE '18%')
order by pcl_mun

​x
 
select distinct pcl_mun from exemptions.modiv_parcels_2015 where (pcl_mun NOT LIKE '10%') or (pcl_mun NOT LIKE '18%')order by pcl_mun​

one would assume this would work but it returns ALL the results

"0233"
"0242"
"1001"
"1002"
"1003"
"1004"
"1005"
"1006"
"1012"
"1013"
"1014"
"1015"
"1018"
"1019"
"1020"
"1024"
"1025"
"1401"
"1402"
"1403"
"1406"
"1407"
"1408"
"1409"
"1412"
"1413"
"1414"
"1415"
"1418"
"1419"
"1420"
"1421"
"1422"
"1423"
"1424"
"1425"
"1426"
"1427"
"1428"
"1429"
"1431"
"1432"
"1433"
"1434"
"1435"
"1436"
"1437"
"1438"
"1439"
"1601"
"1609"
"1611"
"1613"
"1615"
"1801"
"1802"
"1803"
"1807"
"1815"
"1904"
"1906"
"1908"
"1909"
"1911"
"1912"
"1916"
"1918"
"1919"
"1922"
"2101"
"2102"
"2103"
"2105"
"2106"
"2107"
"2108"
"2110"
"2111"
"2112"
"2114"
"2115"
"2116"
"2117"
"2119"
"2120"
"2121"
"2122"
"2123"

 
"0233""0242""1001""1002""1003""1004""1005""1006""1012""1013""1014""1015""1018""1019""1020""1024""1025""1401""1402""1403""1406""1407""1408""1409""1412""1413""1414""1415""1418""1419""1420""1421""1422""1423""1424""1425""1426""1427""1428""1429""1431""1432""1433""1434""1435""1436""1437""1438""1439""1601""1609""1611""1613""1615""1801""1802""1803""1807""1815""1904""1906""1908""1909""1911""1912""1916""1918""1919""1922""2101""2102""2103""2105""2106""2107""2108""2110""2111""2112""2114""2115""2116""2117""2119""2120""2121""2122""2123"​

If i just run each one of those like clauses by themselves they return the correct results. what am I doing wrong here?

Answer

When combining NOT LIKE then AND should be used instead of OR

select distinct pcl_mun 
from exemptions.modiv_parcels_2015 
where pcl_mun NOT LIKE '10%' 
  AND pcl_mun NOT LIKE '18%'
order by pcl_mun

 
select distinct pcl_mun from exemptions.modiv_parcels_2015 where pcl_mun NOT LIKE '10%'   AND pcl_mun NOT LIKE '18%'order by pcl_mun​

It’s a logical thing that can confuse sometimes.

Simplified examples:

('18' NOT LIKE '10%') OR ('18' NOT LIKE '18%')  --> true OR false --> true
('18' NOT LIKE '10%') AND ('18' NOT LIKE '18%')  --> true AND false --> FALSE

('14' NOT LIKE '10%') OR ('14' NOT LIKE '18%')  --> true OR true --> TRUE
('14' NOT LIKE '10%') AND ('14' NOT LIKE '18%')  --> true AND true --> TRUE

 
('18' NOT LIKE '10%') OR ('18' NOT LIKE '18%')  --> true OR false --> true('18' NOT LIKE '10%') AND ('18' NOT LIKE '18%')  --> true AND false --> FALSE​('14' NOT LIKE '10%') OR ('14' NOT LIKE '18%')  --> true OR true --> TRUE('14' NOT LIKE '10%') AND ('14' NOT LIKE '18%')  --> true AND true --> TRUE​

Because it’s different when negating a combination of LIKE‘s.
Then OR should be used instead.

NOT ('18' LIKE '10%' OR '18' LIKE '18%')  --> NOT(false OR true) --> FALSE
NOT ('18' LIKE '10%' AND '18' LIKE '18%')  --> NOT(false AND true) --> true

NOT ('14' LIKE '10%' OR '14' LIKE '18%')  --> NOT(false OR false) --> TRUE
NOT ('14' LIKE '10%' AND '14' LIKE '18%')  --> NOT(false AND false) --> TRUE

 
NOT ('18' LIKE '10%' OR '18' LIKE '18%')  --> NOT(false OR true) --> FALSENOT ('18' LIKE '10%' AND '18' LIKE '18%')  --> NOT(false AND true) --> true​NOT ('14' LIKE '10%' OR '14' LIKE '18%')  --> NOT(false OR false) --> TRUENOT ('14' LIKE '10%' AND '14' LIKE '18%')  --> NOT(false AND false) --> TRUE​

Advertisement

Answer