- Correlated subqueries can specify combinations of equality and inequality between the tables of an outer query and the tables of a subquery.
For example, this SELECT statement specifies an equality condition in its subquery and specifies an inequality condition in the main query:
SELECT * FROM table_1 WHERE x < ALL (SELECT y FROM table_2 WHERE table_2.n = table_1.n);
- You can nest correlated subqueries to a depth of 64.
For example, this SELECT statement specifies three levels of depth:
SELECT * FROM table_1 WHERE x = (SELECT y FROM table_2 WHERE table_2.n = table_1.n AND t2.m = (SELECT y FROM table_3 WHERE table_3.n = table_2.m AND table_1.l = table_3.l));
- A correlated subquery can reference the same table as a table specified in the FROM clause of an outer query.
To differentiate between the references to the inner and outer tables, one of them must be renamed with a correlation name.
In the first example, the outer table is aliased, while in the second example, the inner table is aliased.
Also note that in the first example, the outer reference is a.n, while in the second example, the outer reference is table_1.n.
SELECT * FROM table_1 AS a WHERE x < (SELECT AVG(table_1.x) FROM table_1 WHERE table_1.n = a.n); SELECT * FROM table_1 WHERE x < (SELECT AVG(a.x) FROM table_1 AS a WHERE table_1.n = a.n);
- Correlated subqueries cannot contain SELECT AND CONSUME statements.
- Correlated subqueries cannot contain the TOP n operator.
- You can specify a SELECT in an outer query, a main query, or a subquery.
SELECT Specified Description Main query Any table referenced in the main query should be specified in the FROM clause of the main query SELECT. Subquery Any table referenced in that subquery must be specified either in the FROM clause of that query or in some outer query.
If the select expression list of the subquery specifies a column reference to a table in the FROM clause of an outer query, the column reference must be fully qualified.
If the correlated condition specifies an unqualified column reference, then Vantage searches for the column starting with the tables and views in the current subquery toward the tables and views in the outermost query.
- Correlated subqueries can be specified in a FROM clause under the following conditions:
IF a table referenced in a subquery is identified in the FROM clause of … THEN the table reference is … a subquery local and the subquery is noncorrelated. an outer query not local and the subquery is correlated.
- A FROM clause is required in any subquery specification.
- The FROM clause is not required in the outer query. However, you should always write your applications to specify a FROM clause because future releases might enforce the ANSI SQL:2011 rule that all tables referenced in a SELECT must be specified in a FROM clause. More importantly, specifying tables explicitly in the FROM clause helps to prevent errors in coding your queries because it enhances their clarity.
Vantage returns a warning message if you do not specify a referenced table in the FROM clause.