Complex Queries

Complex Queries

Complex queries are often hard or impossible to write as a single SQL block or a union/intersection/difference of SQL blocks. (An SQL block consists of a single select from where statement, possibly with groupby and having clauses.) We study here two ways of composing multiple SQL blocks to express a complex query: derived relations and the with clause.

Derived Relations

SQL allows a subquery expression to be used in the from clause. If we use such an expression, then we must give the result relation a name, and we can rename the attributes. We do this renaming by using the as clause. For example, consider the subquery

(select branch-name, avg (balance)

from account

group by branch-name)

as result (branch-name, avg-balance)

This subquery generates a relation consisting of the names of all branches and their corresponding average account balances. The subquery result is named result, with the attributes branch-name and avg-balance.

To illustrate the use of a subquery expression in the from clause, consider the query “Find the average account balance of those branches where the average ac- count balance is greater than $1200.” We wrote this query in Section 4.4 by using the

having clause. We can now rewrite this query, without using the having clause, as follows:

Note that we do not need to use the having clause, since the subquery in the from clause computes the average balance, and its result is named as branch-avg; we can use the attributes of branch-avg directly in the where clause.

As another example, suppose we wish to find the maximum across all branches of the total balance at each branch. The having clause does not help us in this task, but we can write this query easily by using a subquery in the from clause, as follows:

The with Clause

Complex queries are much easier to write and to understand if we structure them by breaking them into smaller views that we then combine, just as we structure pro- grams by breaking their task into procedures. However, unlike a procedure definition, a create view clause creates a view definition in the database, and the view definition stays in the database until a command drop view view-name is executed.

The with clause provides a way of defining a temporary view whose definition is available only to the query in which the with clause occurs. Consider the following query, which selects accounts with the maximum balance; if there are many accounts with the same maximum balance, all of them are selected.

The with clause introduced in SQL:1999, is currently supported only by some data- bases.

We could have written the above query by using a nested subquery in either the from clause or the where clause. However, using nested subqueries would have made the query harder to read and understand. The with clause makes the query logic clearer; it also permits a view definition to be used in multiple places within a query.

For example, suppose we want to find all branches where the total account deposit is less than the average of the total account deposits at all branches. We can write the query using the with clause as follows.

We can, of course, create an equivalent query without the with clause, but it would be more complicated and harder to understand. You can write the equivalent query as an exercise.