SQL Match ANY versus ALL


Example

Match any:
Must match at least one string. In this example the product type must be either 'electronics', 'books', or 'video'.

SELECT *
FROM   purchase_table
WHERE  product_type LIKE ANY ('electronics', 'books', 'video');

Match all (must meet all requirements).
In this example both 'united kingdom' and 'london' and 'eastern road' (including variations) must be matched.

SELECT *
FROM   customer_table
WHERE  full_address LIKE ALL ('%united kingdom%', '%london%', '%eastern road%');

Negative selection:
Use ALL to exclude all items.
This example yields all results where the product type is not 'electronics' and not 'books' and not 'video'.

SELECT *
FROM   customer_table
WHERE  product_type NOT LIKE ALL ('electronics', 'books', 'video');