Multiple-result Subqueries
Go Up to InterBase Quick Start: Part IV - Retrieving Data
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:
