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
.