What is a candidate row in SQL

Correlated and uncorrelated subqueries

A subquery can contain a reference to an object that is defined in a parent statement. This is called external reference designated. A subquery that contains an outer reference is called a correlated subquery designated. Correlated subqueries cannot be evaluated independently of the outer query because the subquery uses values ​​from the parent statement. That is, the subquery is run for every row in the parent statement. As a result, the results of the subquery depend on the active row that is evaluated in the parent statement.

For example, the subquery in the statement below returns a value that depends on the active row in the Products table:

In this example, the Products ID column in this subquery is the outer reference. The query gets the names and descriptions of the products whose inventory is less than twice the average ordered quantity of the product tested by the WHERE clause in the main query. The subquery does this by searching the SalesOrderItems table. However, the Products.ID column in the WHERE clause of the subquery refers to a column in the table from the FROM clause of the Head-Query, not the subquery. As the database server steps through each row of the Products table, it uses the ID value of the current row in evaluating the WHERE clause of the subquery.

A query will run without error if a column referenced in a subquery does not exist in the table referenced by the FROM clause of the subquery but exists in a table referenced by the FROM clause of the outer query. SQL Anywhere implicitly qualifies the column in the subquery with the table name of the outer query.

A subquery that does not contain references to objects in a parent statement becomes uncorrelated subquery called. In the example below, the subquery calculates exactly one value: the average quantity from the SalesOrderItems table. When evaluating the query, the database server calculates the value only once and compares each value in the Quantity field in the Products table with it to determine whether to select the appropriate row.