The Vertica Forum is getting a makeover! The site will be undergoing maintenance from Tuesday 8/13 to Friday 8/16. We appreciate your patience and cooperation during this time.

Dealing with Subquery Restrictions

Jim_KnicelyJim_Knicely Administrator
edited August 2018 in Tips from the Team

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/9.1.x/HTML/index.htm#Authoring/AnalyzingData/Queries/Subqueries/SubqueryRestrictions.htm

Have fun!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.