teradata logo

The difference between LIKE ANY and LIKE ALL in Teradata

Finance et Excel

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.

Fiverr, la plateforme avec des milliers d'offres de freelances

Découvrez notre séléction de produits pour les particuliers et professionnels.

2 thoughts on “The difference between LIKE ANY and LIKE ALL in Teradata

  1. Hey ,

    Je vois le site http://www.reportingbusiness.fr et son impressionnant. Je me demande si le contenu ou les bannières des options de publicité disponibles sur votre site?

    Quel sera le prix si nous souhaitons mettre un article sur votre site?

    Note: L’article ne doit pas être un texte comme sponsorisé ou faire de la publicité ou comme ça

    À votre santé
    anto desouza

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *