# ANY and ALL

You typically use comparison operators (`=`, `>`, `<` , etc.) only on subqueries that return one row. With `ANY` and `ALL` operators, you can make comparisons on subqueries that return multiple rows.

These subqueries take the following form:

`expression comparison-operator { ANY | ALL } (subquery)`

`ANY` and `ALL` evaluate whether any or all of the values returned by a subquery match the left-hand expression.

## Equivalent Operators

You can use following operators instead of `ANY` or `ALL`:

This operator... Is equivalent to:
`SOME` `ANY`
`IN` `= ANY`
`NOT IN` `<> ALL`

## Example Data

Examples below use the following tables and data:

 `CREATE TABLE t1 (c1 int, c2 VARCHAR(8));` `CREATE TABLE t2 (c1 int, c2 VARCHAR(8));` ```=> SELECT * FROM t1 ORDER BY c1; c1 | c2 ----+----- 1 | cab 1 | abc 2 | fed 2 | def 3 | ihg 3 | ghi 4 | jkl 5 | mno (8 rows) ``` ```=> SELECT * FROM t2 ORDER BY c1; c1 | c2 ----+----- 1 | abc 2 | fed 3 | jkl 3 | stu 3 | zzz (5 rows) ```

## ANY Subqueries

Subqueries that use the `ANY` keyword return true when any value retrieved in the subquery matches the value of the left-hand expression.

Examples

An `ANY` subquery within an expression:

```=> SELECT c1, c2 FROM t1 WHERE COALESCE((t1.c1 > ANY (SELECT c1 FROM t2)));
c1 | c2
----+-----
2 | fed
2 | def
3 | ihg
3 | ghi
4 | jkl
5 | mno
(6 rows)		```

`ANY` noncorrelated subqueries without aggregates:

```=> SELECT c1 FROM t1 WHERE c1 = ANY (SELECT c1 FROM t2) ORDER BY c1;
c1
----
1
1
2
2
3
3
(6 rows)
```

`ANY` noncorrelated subqueries with aggregates:

```=> SELECT c1, c2 FROM t1 WHERE c1 <> ANY (SELECT MAX(c1) FROM t2) ORDER BY c1;
c1 | c2
----+-----
1 | cab
1 | abc
2 | fed
2 | def
4 | jkl
5 | mno
(6 rows)

=> SELECT c1 FROM t1 GROUP BY c1 HAVING c1 <> ANY (SELECT MAX(c1) FROM t2) ORDER BY c1;
c1
----
1
2
4
5
(4 rows)	```

`ANY` noncorrelated subqueries with aggregates and a `GROUP BY` clause:

```=> SELECT c1, c2 FROM t1 WHERE c1 <> ANY (SELECT MAX(c1) FROM t2 GROUP BY c2) ORDER BY c1;
c1 | c2
----+-----
1 | cab
1 | abc
2 | fed
2 | def
3 | ihg
3 | ghi
4 | jkl
5 | mno
(8 rows)
```

`ANY` noncorrelated subqueries with a `GROUP BY` clause:

```=> SELECT c1, c2 FROM t1 WHERE c1 <=> ANY (SELECT c1 FROM t2 GROUP BY c1) ORDER BY c1;
c1 | c2
----+-----
1 | cab
1 | abc
2 | fed
2 | def
3 | ihg
3 | ghi
(6 rows)

```

`ANY` correlated subqueries with no aggregates or `GROUP BY` clause:

```=> SELECT c1, c2 FROM t1 WHERE c1 >= ANY (SELECT c1 FROM t2 WHERE t2.c2 = t1.c2) ORDER BY c1;
c1 | c2
----+-----
1 | abc
2 | fed
4 | jkl
(3 rows)
```

## ALL Subqueries

A subquery that uses the `ALL` keyword returns true when all values retrieved by the subquery match the left-hand expression, otherwise it returns false.

Examples

`ALL` noncorrelated subqueries without aggregates:

```=> SELECT c1, c2 FROM t1 WHERE c1 >= ALL (SELECT c1 FROM t2) ORDER BY c1;
c1 | c2
----+-----
3 | ihg
3 | ghi
4 | jkl
5 | mno
(4 rows)
```

`ALL` noncorrelated subqueries with aggregates:

```=> SELECT c1, c2 FROM t1 WHERE c1 = ALL (SELECT MAX(c1) FROM t2) ORDER BY c1;
c1 | c2
----+-----
3 | ihg
3 | ghi
(2 rows)

=> SELECT c1 FROM t1 GROUP BY c1 HAVING c1 <> ALL (SELECT MAX(c1) FROM t2) ORDER BY c1;
c1
----
1
2
4
5
(4 rows)
```

`ALL` noncorrelated subqueries with aggregates and a `GROUP BY` clause:

```=> SELECT c1, c2 FROM t1 WHERE c1 <= ALL (SELECT MAX(c1) FROM t2 GROUP BY c2) ORDER BY c1;
c1 | c2
----+-----
1 | cab
1 | abc
(2 rows)
```

`ALL` noncorrelated subqueries with a `GROUP BY` clause:

```=> SELECT c1, c2 FROM t1 WHERE c1 <> ALL (SELECT c1 FROM t2 GROUP BY c1) ORDER BY c1;
c1 | c2
----+-----
4 | jkl
5 | mno
(2 rows)		```

## NULL Handling

Vertica supports multicolumn `<> ALL` subqueries where the columns are not marked `NOT NULL`. If any column contains a `NULL` value, Vertica returns a run-time error.

Vertica does not support `= ANY` subqueries that are nested within another expression if any column values are `NULL`.