Subqueries in Sequel

Subqueries Explained

A subquery is a query within a query—a SELECT statement nested within a SELECT statement. Using subqueries is an effective way to compare values from one file against values in another file. The subquery is placed in the WHERE clause or the HAVING clause of the outer SELECT statement, and returns a single value or a list of values to be used for comparison in the condition.

SEQUEL can perform several different types of subqueries. The examples that follow illustrate the most commonly used types of subqueries.

Basic Comparison

The simplest subquery form involves comparing a field or expression result to the result of a select state- ment. Basic subqueries can use one of six comparison operators (=, <>, <, <=, >, >=), and must satisfy two rules:

the subquery SELECT clause must specify exactly one field, and
the subquery must return only one record (or none)

This example returns a list of customers, and their orders placed in 1999, that have an order value less than the average of all orders placed in 1998.

SELECT cusno, cname, ordno, orval
FROM sequelex/custmast, sequelex/ordhead
JOIN cusno.custmast = cusno.ordhead
WHERE COOYR = 99 AND orval <
   (SELECT AVG(orval)
   FROM ordhead
   WHERE COOYR = 98)

Quantified Comparison

A simple variation of the basic subquery comparison allows several records to be included by the sub- query. By using the reserved word SOME, ANY, or ALL prior to the subquery, a set of values can be compared against a field or expression result, much like the IN comparison.

This example returns a list of customers and their orders placed in 1999 that have an order value less than every order placed in 2002.

SELECT cusno, cname, ordno, orval
FROM sequelex/custmast, sequelex/ordhead
JOIN custmast.cusno=ordhead.cusno
WHERE COOYR = 99 AND orval < ALL
   (SELECT orval
   FROM sequelex/ordhead
   WHERE COOYR = 02)

This example returns a list of customers who have the same preferred effective date as any customers in Region 10.

SELECT cusno, cname, pdate
FROM sequelex/custmast
WHERE pdate = ANY
   (SELECT pdate
   FROM sequelex/custmast
   WHERE regon = 20)

IN Comparison

The subquery IN comparison provides a simple and familiar way of testing a field or expression result against a list of values. Like the basic and quantified subqueries, the IN subquery must identify only a single column in the SELECT clause.

This example returns a list of customers who have submitted orders greater than $5,000. The data from the second table is being used only for the condition, so there is no need to join the two tables.

SELECT cusno, cname
FROM sequelex/custmast
WHERE cusno IN
   (SELECT cusno
   FROM sequelex/ordhead
   WHERE orval > 5000)

This example returns a list of customers who placed orders in 1998, but who did not place an order in 1999.

SELECT cusno, cname, ordno, orval, cooyr
FROM sequelex/custmast, sequelex/ordhead
JOIN custmast.cusno=ordhead.cusno
WHERE COOYR = 98 AND cusno NOT IN
   (SELECT cusno
   FROM sequelex/ordhead
   WHERE COOYR = 99)

EXISTS and NOT EXISTS Conditions

The EXISTS test is the most powerful subquery operator. Unfortunately, it can also lead to the most complicated query statements. It is used simply to determine if the subquery that follows it returns any rows at all. This condition returns true if the subquery returns one or more rows, and returns false if the subquery returns no rows. The specific values returned by the subquery are inconsequential and never used in the query.

This example returns a list of customers who have ever placed an order (that is, have one or more records in the order file).

SELECT cusno, cname
FROM sequelex/custmast cust
WHERE EXISTS
   (SELECT *
   FROM sequelex/ordhead ord
   WHERE cust.cusno = ord.cusno)

In most cases, subquery statements can be avoided by expressing the query as a join or by using mul- tiple steps that involve the creation of intermediate results. Occasionally however, you may find that the power provided by the subquery operators is exactly what is needed to solve a query problem.