 Terminal | Web Home  //  Play

# ANY(), ALL() and IN()

Difficulty: Beginner
Estimated Time: 5 minutes

#### Manticoresearch - ANY(), ALL() and IN()

In this tutorial, we'll explore a few functions available in Manticore Search to handle arrays and conditions

Step 1 of 4

## Preparing a test table

Manticore has a number of functions to handle the multi-value(MVA) data type. More details on the datatype can be found here.

Let's see at the examples of how the ANY(), ALL() and IN() functions can be used with MVA-attributes.

First, connect to Manticore using the MySQL client:

`mysql -P9306 -h0`

Create a test table with a multi-valued attribute:

`DROP TABLE IF EXISTS t;`

`CREATE TABLE t(m multi);`

And populate it with a few values:

`INSERT INTO t VALUES(1, (1)),(2, (2)),(3, (3)),(12,(1,2)), (123, (1,2,3)), (23, (2,3)), (13, (1, 3));`

`SELECT * FROM t;`

## ANY()

Let's run a simple conditional search on the table we've just created which finds documents with 1 in m:

`SELECT * FROM t WHERE m = 1;`

In fact, such query performs the ANY(m) call behind the scenes and, additionally, triggers a warning. We can see this warning by running the SHOW WARNINGS statement:

`SHOW WARNINGS;`

To avoid warnings, we can call the ANY(m) function in the query instead:

`SELECT * FROM t WHERE ANY(m) = 1;`

`SHOW WARNINGS;`

As we see, it produces the same result, but it doesn't result in a warning since ANY(m) has been specified explicitly.

The following query finds documents with at least one value in m equaling 1 or 3:

`SELECT * FROM t WHERE ANY(m) = 1 OR ANY(m) = 3;`

The following query finds documents with at least one value in m equaling 1 and another value equaling 2:

`SELECT * FROM t WHERE ANY(m) = 1 AND ANY(m) = 2;`

And the following one finds documents with at least one value in m not equaling 1:

`SELECT * FROM t WHERE ANY(m) != 1;`

In this case, all documents except for document #1, which has just a single value 1 in the MVA, are included.

## ALL()

Now let's look at similar examples using the ALL() function.

The following query finds documents with all values in m equaling 1. There's only one such document in our table

`SELECT * FROM t WHERE ALL(m) = 1;`

The following query tries to find documents with all values in m equaling 1 or 2:

`SELECT * FROM t WHERE ALL(m) = 1 OR ALL(m) = 2;`

The following query finds documents with all values in m not equaling 1, meaning that if a document has at least one 1 in the MVA, it will not be a match.

`SELECT * FROM t WHERE ALL(m) != 1;`

## ANY() and ALL() with the IN() function

The ANY and ALL() functions can be used together with the IN() function allowing to apply multi-valued conditions as well.

The following query finds documents with all values in m equaling 1 or 2:

`SELECT * FROM t where ALL(m) IN (1,2);`

And the following one finds documents with at least one value in m equaling 1 or 2:

`SELECT * FROM t where ANY(m) IN (1,2);`

The following query, on the contrary, finds documents with at least one value in m not equaling neither 1 nor 2. I.e., it excludes documents with (1), (2), and (1,2) in the MVA.

`SELECT * from t WHERE ANY(m) NOT IN (1,2);`

And the following one finds documents where all values do not equal neither 1 nor 2. Otherwise, a document will not be a match.

`SELECT * from t WHERE ALL(m) NOT IN (1,2);`