sql: like any vs like all

sql teradata
Adam picture Adam · Nov 7, 2016 · Viewed 31.8k times · Source

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

dnoeth picture dnoeth · Nov 8, 2016

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.

Related questions

SQL SELECT multi-columns INTO multi-variable

I'm converting SQL from Teradata to SQL Server in Teradata, they have the format SELECT col1, col2 FROM table1 INTO @variable1, @variable2 In SQL Server, I found SET @variable1 = ( SELECT col1 FROM table1 ); That only allows a single column/variable …

Read More
What is ROWS UNBOUNDED PRECEDING used for in Teradata?

I am just starting on Teradata and I have come across an Ordered Analytical Function called "Rows unbounded preceding" in Teradata. I tried several sites to learn about the function but all of them uses a complicated example explaining the …

Read More
Find which rows have different values for a given column in Teradata SQL

I am trying to compare two addresses from the same ID to see whether they match. For example: Id Adress Code Address 1 1 123 Main 1 2 123 Main 2 1 456 Wall 2 2 456 Wall 3 1 789 Right 3 2 100 Left I'm just trying to figure out whether the address for each ID …

Read More