Subqueries
A subquery (also known as a nested query) is a SELECT statement contained within one of the following:
•
The WHERE clause or HAVING clause of another SELECT statement.
•
The WHERE clause of an UPDATE or DELETE statement.
A subquery allows you to base the result of a SELECT, UPDATE, or DELETE statement on the output of the nested SELECT statement.
Except in correlated subqueries, when you issue a subquery Pervasive PSQL parses the entire statement and executes the innermost subquery first. It uses the result of the innermost subquery as input for the next level subquery, and so forth.
For more information about expressions you can use with subqueries, refer to the SQL Engine Reference.
Subquery Limitations
A subquery in a WHERE clause becomes part of the search criteria. The following limits apply to using subqueries in SELECT, UPDATE, and DELETE statements:
•
You must enclose the subquery in parentheses.
•
The subquery cannot contain a UNION clause.
•
Unless you use an ANY, ALL, EXISTS, or NOT EXISTS keyword in the WHERE clause of the outer query, the selection list of the subquery can contain only one column name expression.
You can nest several levels of subqueries in a statement. The number of subqueries you can nest is determined by the amount of memory available to Pervasive PSQL.
Correlated Subqueries
A correlated subquery contains a WHERE or HAVING clause that references a column from a table in the outer query’s FROM clause; this column is called a correlated column. To test the results from a subquery against the results from the outer query, or to test for a particular value in a query, you must use a correlated subquery.
Since the correlated column comes from the outer query, its value changes each time a row in the outer query is fetched. Pervasive PSQL then evaluates the expressions in the inner query based on this changing value.
The following example shows the names of courses that provide more credit hours than time actually spent in the class room.
SELECT c.Name, c.Credit_Hours
FROM Course c
WHERE c.Name IN
(SELECT c1.Name
FROM Class cl
WHERE c.Name = cl.Name AND c.Credit_Hours >
(HOUR (Finish_Time - Start_Time) + 1))#
To improve performance, you could rephrase the previous statement as a simple query.c.
SELECT c.Name, c.Credit_Hours
FROM Class c1, Course c
WHERE c1.Name = c.Name AND c.Credit_Hours >
(HOUR (Finish_Time - Start_Time) + 1)#