Increase the Power of SQL? (3)

General Use of Single Valued Subqueries

In standard SQL, a subquery returning a single column and retrieving 0 or 1 rows can be used on the right hand side of a comparison. For example,

1000 < (SELECT amount FROM summary WHERE id = 34)

If the subquery returns 1 row, the value of the single column is used as the result of the subquery. If the subquery retrieves no rows, null is used. If more than 1 row is retrieved, an error is given. This is known as a Single Valued Subquery. It returns a scalar value.

FirstSQL greatly extendes the useability of single valued subqueries by allowing them anywhere a scalar value can occur. Single valued subqueries can used in:

Expressions

SELECT lists

WHERE & HAVING clauses

Right hand side of SET expressions in UPDATE

VALUES list in INSERT

Both sides of comparisons

While a single valued subquery can contain any query returning a single column and no more than 1 row, a commonly used query is one involving an aggregate function. For example,

SELECT SUM(amount) FROM invoice

Outer references can be used in single valued subqueries. To retrieve customers and the invoice total for each customer, the following query could be used:

SELECT customer.*,
       (SELECT SUM(amount)
        FROM   invoice
        WHERE  invoice.cust_id = customer.cust_id)
FROM   customer ;

Useful operations with single valued subqueries include:

Update a table column with data from another table
Retrieve aggregate information from another table on the SELECT list (see the example directly above)
Use a subquery in an arithmetic expression
Compare 2 subqueries

The final section, Other FirstSQL Extensions, describes additional FirstSQL extensions to standard SQL.

Return to Contents Page: Increase SQL Power? Return to Can your Database do this?