InterBase Quick Start: Part IV - Using Subqueries

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

Subqueries are a special case of the WHERE clause, but they are an important tool and deserve a discussion of their own.

Recall that in a WHERE clause, you provide a column name, a comparative operator, and a value. WHERE tests the column contents against the value using the operator. You can use a SELECT statement in place of the value portion of a WHERE clause. This internal SELECT clause is the subquery. InterBase executes the SELECT subquery and uses its result set as the value for the WHERE clause.

Suppose, for example, that you want to retrieve a list of employees who work in the same country as a particular employee whose ID is 144. If you do not use a subquery, you would first need to find out what country this employee works in:

SELECT job_country FROM Employee
     WHERE emp_no = 144

This query returns “USA.” With this information, you would issue a second query to find a list of employees in the USA, the same country as employee number 144:

SELECT emp_no, last_name FROM Employee
     WHERE job_country = 'USA'

Using a subquery permits you to perform both queries in a single statement.

Using a Subquery to Find a Single Item

You can obtain the same result by combining the two queries:

 SELECT emp_no, last_name FROM Employee  
       WHERE job_country = (SELECT job_country FROM Employee WHERE emp_no = 144)

In this case, the subquery retrieves a single value, “USA.” The main query interprets “USA” as a value to be tested by the WHERE clause. The subquery must return a single value because the WHERE clause is testing for a single value (“=”); otherwise the statement produces an error.

The result set for this query is a list of 33 employee numbers and last names. These are the employees who work in the same country as employee number 144.

Multiple-result Subqueries

If a subquery returns more than one value, the WHERE clause that contains it must use an operator that tests against more than one value. IN is such an operator.

Using a Subquery to Find a Collection of Items

Execute the following example to retrieve the last name and department number of all employees whose salary is equal to that of someone in department 623. It uses a subquery that returns all the salaries of employees in department 623. The main query selects each employee in turn and checks to see if the associated salary is in the result set of the subquery.

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

The result set should look like this:

Conditions for Subqueries

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:

• Using Aggregate Functions