Make Comparisons on Subqueries that Return Multiple Rows

Jim_Knicely

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.

Example:

I want to check if an integer is greater than any of the integers a set of rows. If a try to use the “>”comparison operator I’ll get an error:

dbadmin=> SELECT 5 > (SELECT 5 UNION ALL SELECT 6 UNION SELECT 7) "5 is GT 5, 6 or 7)";
ERROR 4840:  Subquery used as an expression returned more than one row

But if I add the “ANY” key word, I get my answer:

dbadmin=> SELECT 5 > ANY (SELECT 5 UNION ALL SELECT 6 UNION SELECT 7) "5 is GT 5, 6 or 7";
5 is GT 5, 6 or 7
-------------------
f
(1 row)

dbadmin=> SELECT 5 > ANY (SELECT 4 UNION ALL SELECT 6 UNION SELECT 7) "5 is GT 4, 6 or 7";
5 is GT 4, 6 or 7
-------------------
t
(1 row)

Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/Queries/Subqueries/ANYSOMEAndALL.htm

Have fun!