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.

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 of a WHERE clause. This internal SELECT clause is the subquery. InterBase executes the SELECT subquery and uses the result as the value for the WHERE clause.

For example, 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 need to perform two queries:

Find out what country this employee works in:
```
SELECT job_country
FROM   Employee
WHERE  emp_no = 144
```
This query returns USA.

Now you can execute a second query to find a list of employees in the USA:

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

Using a Subquery to Find a Single Item

A subquery allows you to perform both above queries in a single statement. The following query is a combination of the above:

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 ({{{1}}}); otherwise the statement produces an error.

The result of 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.

Using a Subquery to Find Multiple Items

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

Execute the following statement to retrieve the last name and department number of all employees whose salary is equal to that of someone in the department 623. It uses a subquery that returns all the salaries of employees in the department 623. The main query selects each employee 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 looks 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 that the subquery returns.
`ANY` or `SOME`	Returns true if a comparison is true for at least one value that the subquery returns.
`EXISTS`	Determines if at least one value exists in the resut set that the subquery returns.
`SINGULAR`	Determines if exactly one value exists in the result set that the subquery returns.

Using ALL

The IN operator tests only against the equality of a list of values. If you want to test some relationship other than equality, you can use ALL. For example, to find out who earns more than the people in the 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 looks like this:

The statement tests against all values in the subquery and retrieves the row if the salary is greater. The manager of the department 623 could use this query to see which company employees earn more than employees from his department.

Using ANY, EXISTS, and SINGULAR

Instead of testing against all values that the subquery returns, you can rewrite the previous statement 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 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