The difference between LIKE ANY and LIKE ALL in Teradata

If you need to write quite often SQL code for analysis purpose, here is one trick that might be useful in case your SQL database is Teradata, you need to understand when  you’re about to use a query using ALL or ANY. Just to avoid mistakes :)

The LIKE ANY function in Teradata is extremely useful when you need to perform a search on a given list of keywords. You don’t need to copy paste the same condition, just by using the ANY, you save plenty of place in your query. Let’s be honest, this is a time saver in most of the cases. However, you shouldn’t use LIKE ANY and LIKE ALL the same way.

Before going further, please find a selection of books to improve your SQL skills:

[aff cat= »livre-sql »]

Like Any and Like All

In a WHERE or  a LIKE clause, you might want / need to filter on multiple entries. One function, very convenient one in Teradata is the LIKE ANY and LIKE ALL. However, if they look like the same, these SQL functions definitely don’t taste the same.

LIKE ANY will be like a succession of OR.

For instance, with like any:

field_entry like '%string_1%'
OR field_entry like '%string_2%'
OR field_entry like '%string_3%'

.. meaning that at least one condition need to satisfy the list to be flagged as true.

LIKE ALL will treat the filters like a succession of AND

For instance:

field_entry like '%string_1%'
AND field_entry like '%string_2%'
AND field_entry like '%string_3%'

… meaning that all the conditions need to be satisfied!

So, in terms of inclusion, you will not end up with the same information being retrieved in the database if you use ANY or ALL.

Depending on the usage and need, you definitely should pay attention to the ANY or ALL word you will use – or you may end-up excluding/integrating entries you should / shouldn’t have integrated in the first place for your analysis.

You can spice this up by using a clause let’s say with NOT LIKE ANY or NOT LIKE ALL which basically will generate the opposite.