Subquery restrictions

The following restrictions apply to Vertica subqueries:.

The following restrictions apply to Vertica subqueries:

Subqueries are not allowed in the defining query of a CREATE PROJECTION statement.

Subqueries can be used in the SELECT list, but GROUP BY or aggregate functions are not allowed in the query if the subquery is not part of the GROUP BY clause in the containing query. For example, the following two statement returns an error message:

=> SELECT y, (SELECT MAX(a) FROM t1) FROM t2 GROUP BY y;
   ERROR:  subqueries in the SELECT or ORDER BY are not supported if the
   subquery is not part of the GROUP BY
=> SELECT MAX(y), (SELECT MAX(a) FROM t1) FROM t2;
   ERROR:  subqueries in the SELECT or ORDER BY are not supported if the
   query has aggregates and the subquery is not part of the GROUP BY

Subqueries are supported within UPDATE statements with the following exceptions:
- You cannot use SET column = {expression} to specify a subquery.
- The table specified in the UPDATE list cannot also appear in the FROM clause (no self joins).
FROM clause subqueries require an alias but tables do not. If the table has no alias, the query must refer its columns as table-name.column-name. However, column names that are unique among all tables in the query do not need to be qualified by their table name.
If the ORDER BY clause is inside a FROM clause subquery, rather than in the containing query, the query is liable to return unexpected sort results. This occurs because Vertica data comes from multiple nodes, so sort order cannot be guaranteed unless the outer query block specifies an ORDER BY clause. This behavior complies with the SQL standard, but it might differ from other databases.
Multicolumn subqueries cannot use the <, >, <=, >= comparison operators. They can use <>, !=, and = operators.
WHERE and HAVING clause subqueries must use Boolean comparison operators: =, >, <, <>, <=, >=. Those subqueries can be noncorrelated and correlated.
[NOT] IN and ANY subqueries nested in another expression are not supported if any of the column values are NULL. In the following statement, for example, if column x from either table t1 or t2 contains a NULL value, Vertica returns a run-time error:
```
=> SELECT * FROM t1 WHERE (x IN (SELECT x FROM t2)) IS FALSE;
ERROR:  NULL value found in a column used by a subquery
```
Vertica returns an error message during subquery run time on scalar subqueries that return more than one row.
Aggregates and GROUP BY clauses are allowed in subqueries, as long as those subqueries are not correlated.
Correlated expressions under ALL and [NOT] IN are not supported.
Correlated expressions under OR are not supported.
Multiple correlations are allowed only for subqueries that are joined with an equality (=) predicate. However, IN/NOT IN, EXISTS/NOT EXISTS predicates within correlated subqueries are not allowed:
```
=> SELECT t2.x, t2.y, t2.z FROM t2 WHERE t2.z NOT IN
       (SELECT t1.z FROM t1 WHERE t1.x = t2.x);
   ERROR: Correlated subquery with NOT IN is not supported
```

Up to one level of correlated subqueries is allowed in the WHERE clause if the subquery references columns in the immediate outer query block. For example, the following query is not supported because the t2.x = t3.x subquery can only refer to table t1 in the outer query, making it a correlated expression because t3.x is two levels out:

=> SELECT t3.x, t3.y, t3.z FROM t3 WHERE t3.z IN (
     SELECT t1.z FROM t1 WHERE EXISTS (
        SELECT 'x' FROM t2 WHERE t2.x = t3.x) AND t1.x = t3.x);
ERROR:  More than one level correlated subqueries are not supported

The query is supported if it is rewritten as follows:

=> SELECT t3.x, t3.y, t3.z FROM t3 WHERE t3.z IN
       (SELECT t1.z FROM t1 WHERE EXISTS
         (SELECT 'x' FROM t2 WHERE t2.x = t1.x)
   AND t1.x = t3.x);