A subquery is a SELECT statement with one or more SELECT statements within it. A subquery produces values for further processing within the statement. The maximum number of nested subqueries allowed within the topmost SELECT statement is 16.
The following types of subqueries are supported:
Correlated subquery predicates in the HAVING clause which contain references to grouped columns are not supported.
Expression subqueries allow the subquery within the SELECT list. For example, SELECT (SELECT SUM(c1) FROM t1 WHERE t1.c2 = t1.(c2) FROM t2. Only one item is allowed in the subquery SELECT list. For example, the following statement returns an error because the subquery SELECT list contains more than one item: SELECT p.id, (SELECT SUM(b.amount_owed), SUM(b.amount_paid) FROM billing b) FROM person p.
A subquery as an expression may be correlated or non-correlated. A correlated subquery references one or more columns in any of the tables in the topmost statement. A non-correlated subquery references no columns in any of the tables in the topmost statement.
The following illustrates an example of a correlated subquery in a WHERE clause:
SELECT * FROM student s WHERE s.Tuition_id IN (SELECT t.ID FROM tuition t WHERE t.ID = s.Tuition_ID)
Note: Correlated subqueries are not allowed in table subqueries; only non-correlated subqueries are allowed in table subqueries.
A subquery connected with the operators IN, EXISTS, ALL, or ANY is not considered an expression.
Both correlated and non-correlated subqueries can return only a single value. For this reason, both correlated and non-correlated subqueries are also referred to as scalar subqueries.
You may use a subquery on the left-hand side of an expression:
Expr-or-SubQuery CompareOp Expr-or-SubQuery
where Expr is an expression, and CompareOp is one of:
Left-hand subquery behavior has been optimized for IN, NOT IN, and =ANY in cases where the subquery is not correlated and any join condition is an outer join. Other conditions may not be optimized. Here is an example of a query that meets these conditions:
SELECT count(*) FROM person WHERE id IN (SELECT faculty_id FROM class)
Performance improves if you use an index in the subquery because Pervasive PSQL optimizes a subquery based on the index. For example, the subquery in the following statement is optimized on student_id because it is an index in the Billing table:
SELECT (SELECT SUM(b.amount_owed) FROM billing b WHERE b.student_id = p.id) FROM person p
UNION in Subquery
Parentheses on different UNION groups within a subquery are not allowed. Parentheses are allowed within each SELECT statement.
For example, the parenthesis following “IN” and the last parenthesis are not allowed the following statement:
SELECT c1 FROM t5 WHERE c1 IN ( (SELECT c1 FROM t1 UNION SELECT c1 FROM t2) UNION ALL (SELECT c1 FROM t3 UNION SELECT c1 from t4) )
Table subqueries can be used to combine multiple queries into one detailed query. A table subquery is a dynamic view, which is not persisted in the database. When the topmost SELECT query completes, all resources associated with table subqueries are released.
Note: Only non-correlated subqueries are allowed in table subqueries. Correlated subqueries are not allowed.
The following example of pagination (1500 rows with 100 rows per page) illustrates the use of table subqueries with an ORDER BY clause:
The first 100 rows
SELECT * FROM ( SELECT TOP 100 * FROM ( SELECT TOP 100 * FROM person ORDER BY last_name asc ) AS foo ORDER BY last_name desc ) AS bar ORDER BY last_name ASC
The second 100 rows
SELECT * FROM ( SELECT TOP 100 * FROM ( SELECT TOP 200 * FROM person ORDER BY last_name asc ) AS foo ORDER BY last_name DESC ) AS bar ORDER BY last_name ASC
The fifteenth 100 rows
SELECT * FROM ( SELECT TOP 100 * FROM ( SELECT TOP 1500 * FROM person ORDER BY last_name ASC ) AS foo ORDER BY last_name DESC ) AS bar ORDER BY last_name ASC