Adam Adam - 1 month ago 18
SQL Question

sql: like any vs like all

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%')

Answer

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.