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:
- Executes the subquery
SELECT state FROM states
(in bold). - 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.
You can use an outer join to obtain the same effect as a correlated subquery.
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:
- The query extracts and evaluates each
addresses.state
value in the outer subquery records. - Then the query—using the EXISTS predicate—checks the addresses in the inner (correlated) subquery.
- 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.