Make Comparisons on Subqueries that Return Multiple Rows
Jim_Knicely
Administrator
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!
0