Noncorrelated and correlated subqueries

Subqueries can be categorized into two types:

• A noncorrelated (simple) subquery obtains its results independently of its containing (outer) statement.

• A correlated subquery requires values from its outer query in order to execute.

Noncorrelated subqueries

A noncorrelated subquery executes independently of the outer query. The subquery executes first, and then passes its results to the outer query, For example:

=> SELECT name, street, city, state FROM addresses WHERE state IN (SELECT state FROM states);

Vertica executes this query as follows:

1. Executes the subquery SELECT state FROM states (in bold).

2. Passes the subquery results to the outer query.

A query's WHERE and HAVING clauses can specify noncorrelated subqueries if the subquery resolves to a single row, as shown below:

In WHERE clause

=> SELECT COUNT(*) FROM SubQ1 WHERE SubQ1.a = (SELECT y from SubQ2);

In HAVING clause

=> SELECT COUNT(*) FROM SubQ1 GROUP BY SubQ1.a HAVING SubQ1.a = (SubQ1.a & (SELECT y from SubQ2)

Correlated subqueries

A correlated subquery typically obtains values from its outer query before it executes. When the subquery returns, it passes its results to the outer query.

In the following example, the subquery needs values from the addresses.state column in the outer query:

=> SELECT name, street, city, state FROM addresses
     WHERE EXISTS (SELECT * FROM states WHERE states.state = addresses.state);

Vertica executes this query as follows:

1. The query extracts and evaluates each addresses.state value in the outer subquery records.

2. Then the query—using the EXISTS predicate—checks the addresses in the inner (correlated) subquery.

3. Because it uses the EXISTS predicate, the query stops processing when it finds the first match.

When Vertica executes this query, it translates the full query into a JOIN WITH SIPS.