Subqueries

Subqueries

Objectives
After completing this lesson, you should be able to
do the following:
• Describe the types of problem that subqueries can
solve
• Define subqueries
• List the types of subqueries

• Write single-row and multiple-row subqueries

Using a Subquery to Solve a Problem
Suppose you want to write a query to find out who earns a salary greater than Abel’s salary.
To solve this problem, you need two queries: one to find what Abel earns, and a second query to find
who earns more than that amount.
You can solve this problem by combining the two queries, placing one query inside the other query.
The inner query or the subquery returns a value that is used by the outer query or the main query.
Using a subquery is equivalent to performing two sequential queries and using the result of the first

query as the search value in the second query.

Subquery Syntax

SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list

FROM table);

• The subquery (inner query) executes once before
the main query.
• The result of the subquery is used by the main

query (outer query).


Subqueries
A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. You
can build powerful statements out of simple ones by using subqueries. They can be very useful when
you need to select rows from a table with a condition that depends on the data in the table itself.
You can place the subquery in a number of SQL clauses, including:
• The WHERE clause
• The HAVING clause
• The FROM clause
In the syntax:
operator includes a comparison condition such as >, =, or IN
Note: Comparison conditions fall into two classes: single-row operators (>, =, >=, <, <>, <=) and
multiple-row operators (IN, ANY, ALL).
The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The
subquery generally executes first, and its output is used to complete the query condition for the main

or outer query.

Using a Subquery

SELECT last_name
FROM employees
WHERE salary >
(SELECT salary
FROM employees

WHERE last_name = ’Abel’);

Guidelines for Using Subqueries
• Enclose subqueries in parentheses.
• Place subqueries on the right side of the
comparison condition.
• The ORDER BY clause in the subquery is not
needed unless you are performing Top-N analysis.
• Use single-row operators with single-row
subqueries and use multiple-row operators with

multiple-row subqueries.

Types of Subqueries
• Single-row subqueries: Queries that return only one row from the inner SELECT statement
• Multiple-row subqueries: Queries that return more than one row from the inner SELECT

statement


Single-Row Subqueries
A single-row subquery is one that returns one row from the inner SELECT statement. This type of
subquery uses a single-row operator. The slide gives a list of single-row operators.
Example
Display the employees whose job ID is the same as that of employee 141.
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees

WHERE employee_id = 141);


SELECT last_name, job_id, salary
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141)
AND salary >
(SELECT salary
FROM employees

WHERE employee_id = 143);


Executing Single-Row Subqueries
A SELECT statement can be considered as a query block. The example on the slide displays
employees whose job ID is the same as that of employee 141 and whose salary is greater than that of
employee 143.
The example consists of three query blocks: the outer query and two inner queries. The inner query
blocks are executed first, producing the query results ST_CLERK and 2600, respectively. The outer
query block is then processed and uses the values returned by the inner queries to complete its search
conditions.
Both inner queries return single values (ST_CLERK and 2600, respectively), so this SQL statement is

called a single-row subquery.


Using Group Functions in a Subquery
SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)

FROM employees);

The HAVING Clause with Subqueries
• The Oracle server executes subqueries first.
• The Oracle server returns results into the HAVING

clause of the main query.


SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees

WHERE department_id = 50);

Example
Find the job with the lowest average salary.
SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
FROM employees

GROUP BY job_id);

Multiple-Row Subqueries
• Return more than one row

• Use multiple-row comparison operators

Operator
IN
ANY

ALL

Multiple-Row Subqueries
Subqueries that return more than one row are called multiple-row subqueries. You use a multiple-row
operator, instead of a single-row operator, with a multiple-row subquery. The multiple-row operator
expects one or more values.
SELECT last_name, salary, department_id
FROM employees
WHERE salary IN (SELECT MIN(salary)
FROM employees

GROUP BY department_id);

Example
Find the employees who earn the same salary as the minimum salary for each department.
The inner query is executed first, producing a query result. The main query block is then processed and
uses the values returned by the inner query to complete its search condition. In fact, the main query would
appear to the Oracle server as follows:
SELECT last_name, salary, department_id
FROM employees

WHERE salary IN (2500, 4200, 4400, 6000, 7000, 8300, 8600, 17000);

Using the ANY Operator in Multiple-Row Subqueries

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE job_id = ’IT_PROG’)

AND job_id <> ’IT_PROG’;

Using the ALL Operator in Multiple-Row Subqueries

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL
(SELECT salary
FROM employees
WHERE job_id = ’IT_PROG’)

AND job_id <> ’IT_PROG’;

Null Values in a Subquery

SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT mgr.manager_id

FROM employees mgr);

Summary

In this lesson, you should have learned how to:

• Identify when a subquery can help solve a

question

• Write subqueries when a query is based on

unknown values