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.
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)
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)
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.