Subqueries

You are looking at an older version of the documentation. The latest version is found here.

A subquery is an SQL query embedded within another SQL query. The query containing the subquery is called an outer query.

The Data Virtuality Server supports the following subquery types:

Scalar subquery - a subquery that returns only a single column with a single value. Scalar subqueries are a type of expression and can be used where single-valued expressions are expected;
Correlated subquery - a subquery that contains a column reference to the outer query;
Uncorrelated subquery - a subquery that contains no references to the outer subquery.

Inline Views

Subqueries in the FROM clause of the outer query (also known as inline views) can return any number of rows and columns. This type of subquery must always be given an alias. An inline view is nearly identical to a traditional view. For more information, please see WITH Clause.

Here is an example:

SQL

SELECT a FROM (SELECT Y.b, Y.c FROM Y WHERE Y.d = '3') AS X WHERE a = X.c AND b = X.b

Subqueries can appear anywhere where an expression or criteria is expected.

Subqueries are supported in quantified criteria, the EXISTS predicate, the IN predicate, and as scalar subqueries:

SQL

SELECT a FROM X WHERE EXISTS (SELECT 1 FROM Y WHERE c=X.a)

Here is a more sophisticated example of quantified comparison subqueries:

SQL

SELECT a FROM X WHERE a >= ANY (SELECT b FROM Y WHERE c=3) 
SELECT a FROM X WHERE a < SOME (SELECT b FROM Y WHERE c=4) 
SELECT a FROM X WHERE a = ALL (SELECT b FROM Y WHERE c=2)

And here is an example with an IN predicate:

SQL

SELECT a FROM X WHERE a IN (SELECT b FROM Y WHERE c=3)

Subqueries can be optimized - for more information, please refer to Subquery Optimization.