Using Subqueries
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 don’t 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.