Dealing with Subquery Restrictions: Quick Tip

Jim Knicely authored this tip. A subquery is a SELECT statement embedded within another SELECT statement. The embedded subquery is often referenced as the query’s inner statement, while the containing query is typically referenced as the query’s statement, or outer query block. One restriction of a subquery is that you cannot have a correlated expressions with an OR. Example: dbadmin=> SELECT * FROM a; c1 | c2 ----+---- 1 | 1 2 | 3 3 | 5 4 | 10 (4 rows) dbadmin=> SELECT * FROM b; c1 | c2 | c3 —-+—-+—- 1 | 1 | 1 2 | 1 | 2 3 | 3 | 4 4 | 10 | 20 (4 rows) dbadmin=> SELECT * FROM a WHERE EXISTS (SELECT NULL FROM b WHERE a.c1 = b.c1 AND a.c2 NOT BETWEEN b.c2 AND b.c3); ERROR 2787: Correlated subquery expressions under OR not supported Wait, I didn’t use an OR statement! Or did I? My query is re-written internally in Vertica as this SQL statement: dbadmin=> SELECT * FROM a WHERE EXISTS (SELECT NULL FROM b WHERE b.c1 = a.c1 AND (a.c2 < b.c2 OR a.c2 > b.c3)); ERROR 2787: Correlated subquery expressions under OR not supported So how do I get around this? Simple. Re-write my query myself: dbadmin=> SELECT * FROM a WHERE NOT EXISTS (SELECT NULL FROM b WHERE a.c1 = b.c1 AND a.c2 BETWEEN b.c2 AND b.c3); c1 | c2 ----+---- 2 | 3 3 | 5 (2 rows) Helpful link: https://my.vertica.com/docs/latest/HTML/index.htm#Authoring/AnalyzingData/Queries/Subqueries/SubqueryRestrictions.htm Have fun!