Conditions for Subqueries

Go Up to InterBase Quick Start: Part IV - Retrieving Data

The following table summarizes the operators that compare a value on the left of the operator to the results of a subquery on the right of the operator:

Operator	Purpose
All	Returns true if a comparison is true for all values returned by a subquery.
ANY or SOME	Returns true if a comparison is true for at least one value returned by a subquery.
EXISTS	Determines if at least one value exists in the resut set returned by a subquery.
SINGULAR	Determines if exactly one value exists in the result set returned by a subquery.

InterBase Comparison Operators that Require Subqueries

Using ALL

The IN operator tests only against the equality of a list of values. What if you want to test some relationship other than equality? For example, suppose you want to find out who earns more than the people in department 623. Enter the following query:

 SELECT last_name, salary FROM Employee   
       WHERE salary > ALL  
       (SELECT salary FROM Employee WHERE dept_no = 623)

The result set should look like this:

This example uses the ALL operator. The statement tests against all values in the subquery and retrieves the row if the salary is greater. The manager of department 623 can use this output to see which company employees earn more than his or her employees.

Using ANY, EXISTS, and SINGULAR

Instead of testing against all values returned by a subquery, you can rewrite the example to test for at least one value:

SELECT last_name, salary FROM Employee  
       WHERE salary > ANY   (SELECT salary FROM Employee WHERE dept_no = 623)

This statement retrieves 34 rows for which salary is greater than any of the values from the subquery. The ANY keyword has a synonym, SOME. The two are interchangeable.

Two other subquery operators are EXISTS and SINGULAR.

• For a given value, EXISTS tests whether at least one qualifying row meets the search condition specified in a subquery. EXISTS returns either true or false, even when handling NULL values.
• For a given value, SINGULAR tests whether exactly one qualifying row meets the search condition specified in a subquery.

Advance To:

• InterBase Quick Start: Part IV - Using Aggregate Functions