Correlated subqueries in Netezza Performance Server SQL

Both regular and correlated sub queries are supported. Whenever Netezza Performance Server SQL encounters a regular subquery, it precalculates the subquery once as the example in Subqueries. When the system encounters correlated subqueries in WHERE restrictions, it transforms them internally to equivalent join formats as in the second example in Correlated subqueries.

If you choose to use correlated subqueries, keep in mind the following restrictions on the form and placement of correlated subqueries:

You can use correlated subqueries in WHERE clauses.
You can use correlated subqueries in inner join conditions and with the equal join condition operator.
You can use correlated subqueries in mixed correlated expressions only in the following form:
```
   expr(corr_columA, corr_columnB,...) = expr(local_columnX, 
local_columnY,...)
```
You cannot use correlated subqueries in SET operations (UNION, INTERSECT, EXCEPT, and MINUS).
You cannot use correlated subqueries in aggregates with GROUP BY and HAVING clauses.
You cannot use correlated subqueries in ORed clauses or in CASE/WHEN expressions.
You cannot use correlated subqueries in IN lists.
You cannot use correlated subqueries in SELECT lists.

Note: Because correlated subqueries can drastically affect query performance, consider replacing them with joins for more efficient code whenever possible.