Writing single-row and multiple-row subqueries
This chapter excerpt explains how to use and write single-row and multiple-row subqueries in SQL, with sample code and exercises.
OCA Oracle Database 11g: SQL Fundamentals I Exam Guide
Chapter 8: Using Subqueries to Solve Problems
![]() |
|
![]() |
![]() |
![]() |
Table of contents:
Define SQL subqueries
Using subqueries in SQL
List the types of SQL subqueries
Writing single-row and multiple-row subqueries
Following are examples of single- and multiple-row subqueries. They are based on the HR demonstration schema. How would you figure out which employees have a manager who works for a department based in the United Kingdom? This is a possible solution, using multiple-row subqueries:
select last_name from employees where manager_id in (select employee_id from employees where department_id in (select department_id from departments where location_id in (select location_id from locations where country_id='UK')));
In the preceding example, subqueries are nested three levels deep. Note that the subqueries use the IN operator because it is possible that the queries could return several rows.
You have been asked to find the job with the highest average salary. This can be done with a single-row subquery:
select job_title from jobs natural join employees group by job_title having avg(salary) = (select max(avg(salary)) from employees group by job_id);
The subquery returns a single value: the average salary of the department with the highest average salary. It is safe to use the equality operator for this subquery because the MAX function guarantees that only one row will be returned.
The ANY and ALL operators are supported syntax, but their function can be duplicated with other more commonly used operators combined with aggregations. For example, these two statements, which retrieve all employees whose salary is above that of anyone in department 80, will return identical result sets:
![]() |
||||
|
![]() |
|||
![]() |
select last_name from employees where salary > all (select salary from employees where department_id=80); select last_name from employees where salary > (select max(salary) from employees where department_id=80);
The following table summarizes the equivalents for ANY and ALL:
Operator | Meaning |
---|---|
< ANY | less than the highest |
> ANY | not equal to any member in a list |
ANY
|
more than the lowest |
= ANY | equivalent to IN |
> ALL | more than the highest |
< ALL | less than the lowest |
EXERCISE 8-4
Write a Query That Is Reliable and User Friendly
In this exercise, develop a multi-row subquery that will prompt for user input. Use either SQL*Plus or SQL Developer. All the queries should be run when connected to the HR schema; it is assumed that the tables have the standard sets of rows.
- Log on to your database as user HR.
- Design a query that will prompt for a department name and list the last name of every employee in that department:
select last_name from employees where department_id = (select department_id from departments where department_name = '&Department_name');
- Run the query in step 2 three times, when prompted supplying these values: first time, Executive
second time, executive
third time, Executiv
The following illustration shows the result, using SQL*Plus: - Note the results from step 3. The first run succeeded because the value entered was an exact match, but the other failed. Adjust the query to make it more user friendly, so that it can handle minor variations in case or spelling:
select last_name from employees where department_id = (select department_id from departments where upper(department_name) like upper('%&Department_name%'));
- Run the query in step 4 three times, using the same values as used in step 3. This time, the query will execute successfully.
- Run the query in step 4 again, and this time enter the value Pu. The query will fail, with an "ORA-01427: single-row subquery returns more than one row" error, because the attempt to make it more user-friendly means that the subquery is no longer guaranteed to be a single-row subquery. The string Pu matches two departments.
- Adjust the query to make it resilient against the ORA-01427 error, and adjust the output to prevent any possible confusion:
select last_name,department_name from employees join departments on employees.department_id = departments.department_id where departments.department_id in (select department_id from departments where upper(department_name) like upper('%&Department_name%'));
The following illustration shows this final step: code that is approaching the ideal of being both bullet proof and user friendly:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Download the chapter "Using Subqueries to Solve Problems", which includes practice exercises and a self-test, in PDF form.