How to Use Subqueries in Oracle SQL

A subquery is a query inside another statement, written in parentheses. It lets one question depend on the answer to another: employees who earn more than the average, airports that are destinations of the longest routes, the best-paid person in each department. Depending on where you put it and how many rows and columns it returns, a subquery supplies a single value, a list of values, or a whole table.

This guide covers single-row, multi-row, and multi-column subqueries in WHERE, and inline views in FROM, with the errors and traps that come with each.

Code for This Guide

The main examples are in the examples/subqueries folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Kinds of Subquery

Subqueries that refer to a column of the outer query are correlated, evaluated for each outer row; the examples below are all uncorrelated, evaluated once.

Single-Row Subqueries

A subquery compared with a comparison operator must return at most one row. Oracle evaluates it once and uses its result like a literal. This query finds the cabin crew (department 30) who earn more than the average employee of the whole airline.

Example:

select last_name, salary
from   employees
where  salary > (select avg(salary) from employees)
and    department_id = 30;

Output:

LAST_NAME       SALARY
____________ _________
Rahman           22000

If the subquery can return more than one row, the statement fails at run time. Here, grouping by department returns one average per department:

Example:

select last_name, salary
from   employees
where  salary > (select avg(salary) from employees group by department_id);

Output:

Error starting at line : 1
In command -
select last_name, salary
from   employees
where  salary > (select avg(salary) from employees group by department_id)
Error at Command Line : 3 Column : 18
Error report -
SQL Error: ORA-01427: single-row subquery returns more than one row

Fix it by making the subquery return one value, or by switching to a multi-row operator. A single-row subquery that returns no rows yields NULL, so the comparison is not true for any row and the query returns nothing.

Multi-Row Subqueries with IN

With IN, the subquery may return any number of rows. This query lists the airports that are destinations of routes longer than 12,000 km.

Example:

select airport_code, city
from   airports
where  airport_code in (select destination from routes where distance_km > 12000);

Output:

AIRPORT_CODE    CITY
_______________ ______________
AKL             Auckland
DXB             Dubai
GRU             São Paulo
LAX             Los Angeles
SYD             Sydney

Be careful with NOT IN: if the subquery returns a single NULL, NOT IN returns no rows at all. Filter NULLs out of the subquery, or use NOT EXISTS.

ANY and ALL

ANY and ALL compare a value with every row of a subquery. > ALL means greater than the largest value, and > ANY means greater than at least one value, that is, greater than the smallest.

Example:

-- earns more than everyone in department 90
select last_name, department_id, salary
from   employees
where  salary > all (select salary from employees where department_id = 90)
and    department_id = 30
order  by salary desc;

-- earns more than at least one person in department 90
select count(*) as staff
from   employees
where  salary > any (select salary from employees where department_id = 90)
and    department_id = 30;

Output:

LAST_NAME       DEPARTMENT_ID    SALARY
____________ ________________ _________
Rahman                     30     22000

   STAFF
________
      11

Only one cabin crew member out-earns everyone in department 90, but all eleven out-earn at least one person there. = ANY is the same as IN.

Multi-Column Subqueries

A subquery can return several columns, compared with a list of columns in parentheses. Matching (department_id, salary) pairs against each department's maximum finds the best-paid employee of every department.

Example:

select last_name, department_id, salary
from   employees
where  (department_id, salary) in (select department_id, max(salary)
                                   from employees group by department_id)
order  by department_id;

Output:

LAST_NAME       DEPARTMENT_ID    SALARY
____________ ________________ _________
Haddad                     10     48000
Clarke                     20     27000
Rahman                     30     22000
Evans                      40     34000
Mehta                      50     33000
Okafor                     60     29000
Fernández                  70     23000
Smith                      80     15800
Chen                       90     12000
Nguyen                    100     21500

10 rows selected.

Comparing the pair matters: salary in (select max(salary) ...) alone would also match someone in another department who happens to earn another department's maximum.

Inline Views in FROM

A subquery in FROM is an inline view: a table computed for the query, which the outer query can join, filter, and group like any other. Here it totals staff and payroll per department, and the outer query joins the result to DEPARTMENTS and keeps the larger departments.

Example:

select dept.department_name, s.staff, s.payroll
from   (select department_id, count(*) as staff, sum(salary) as payroll
        from employees group by department_id) s
join   departments dept on dept.department_id = s.department_id
where  s.staff >= 10;

Output:

DEPARTMENT_NAME           STAFF    PAYROLL
______________________ ________ __________
Flight Operations            12     213800
Cabin Services               11      99100
Sales and Marketing          10     142100

The outer WHERE can filter on staff, an aggregate computed inside the view, without HAVING. When inline views grow long or are needed twice, name them with the WITH clause instead.

Related Guides

• How to Filter Groups with HAVING in Oracle SQL
• How to Use WHERE Clause in Oracle Database 23ai Queries

Conclusion

Use a single-row subquery with comparison operators when it returns one value, IN, ANY, or ALL when it returns a list, a column list in parentheses when it returns pairs, and an inline view in FROM when you need a computed table. Watch for ORA-01427 when a single-row subquery returns several rows, and for NULLs inside NOT IN.