Make Comparisons on Subqueries that Return Multiple Rows

Posted May 7, 2019 by James Knicely, Vertica Field Chief Technologist

Helpful Tips message on post-it note
You typically use comparison operators (such as =, >, < ) 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 in a set of rows. If I 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 keyword, 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 Links:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/AnalyzingData/Queries/Subqueries/ANYSOMEAndALL.htm

Have fun!