sql: like any vs like all

Question

I can't figure out why sometimes LIKE requires ANY and other times it requires ALL, and it's making me crazy. I feel like I should be able to use ANY in both conditions (I'm trying to select records following any of the regex expressions in parentheses).

For some reason, the first LIKE, with ANY, works just fine - it returns all records with dog chow, pedigree, or beneful.

The second LIKE, however, requires ALL. Otherwise it won't leave out records with treat, supplies or wet. But why? I feel like ANY is the appropriate form here.

where dsc_item like any ('%DOG CHOW%','%PEDIGREE%','%BENEFUL%')
and dsc_comm not like all ('%TREATS%','%SUPPLIES%', '%WET%')

score 11 · Accepted Answer · answered Nov 07 '16 at 23:12

LIKE ANY translates to ORed condition, but LIKE ALL to AND:

where
 (    dsc_item like '%DOG CHOW%'
   OR dsc_item like '%PEDIGREE%','%BENEFUL%'
 )
and
 (     dsc_comm not like '%TREATS%' 
   AND dsc_comm not like '%SUPPLIES%'
   AND dsc_comm not like '%WET%'
 )

If you replace the AND with OR it's like col <> 1 OR col <> 2 which is true for every non-NULL row.

score 2 · Answer 2 · answered Nov 07 '16 at 23:26

like any        similar to      = any  
like all        similar to      = all  
not like any    similar to      <> any  
not like all    similar to      <> all

select      'My name is Inigo Montoya, you killed mhy father, prepare to die!'  as str

           ,case when str like any ('%Inigo%','%Donald%' ,'%Hillary%')      then 1 else 0 end -- 1
           ,case when str like any ('%Adam%' ,'%Donald%' ,'%Hillary%')      then 1 else 0 end -- 0
           ,case when str like all ('%Inigo%','%Montoya%','%father%')       then 1 else 0 end -- 1
           ,case when str like all ('%Inigo%','%Montoya%','%mother%')       then 1 else 0 end -- 0

           ,case when str not like any ('%Inigo%','%Montoya%','%mother%')   then 1 else 0 end -- 1
           ,case when str not like any ('%Inigo%','%Montoya%','%father%')   then 1 else 0 end -- 0
           ,case when str not like all ('%Adam%' ,'%Donald%' ,'%Hillary%')  then 1 else 0 end -- 1
           ,case when str not like all ('%Inigo%','%Donald%' ,'%Hillary%')  then 1 else 0 end -- 0
;

sql: like any vs like all

2 Answers2

Linked