Database Programming with

SQL
10-4
Correlated Subqueries

Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

Objectives
• This lesson covers the following objectives:
−Identify when correlated subqueries are needed
−Construct and execute correlated subqueries
−Create a query using the EXISTS and NOT EXISTS operators to
test for returned rows from the subquery
−Construct and execute named subqueries using the WITH
clause

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 3

3
Purpose
• Sometimes you have to answer more than one
question in one sentence
• Your friend might ask you if you have enough money
for a cinema ticket, popcorn, and a drink
• Before you can answer your friend, you need to know
the prices of the ticket, the popcorn, and the drink
• You also need to see how much money you have in
your pocket
• So actually, what seemed like an easy question, turns
into four questions that you need answers to before
you can say "Yes" or "No"

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 4

4
Purpose
• In business, you might get asked to produce a report of
all employees earning more than the average salary for
their departments
• So here you first have to calculate the average salary
per department, and then compare the salary for each
employee to the average salary of that employee's
department

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 5

5
Correlated Subqueries
• The Oracle server performs a correlated subquery
when the subquery references a column from a table
referred to in the parent statement
GET
candidate row from outer query

EXECUTE
inner query using candidate row value

USE
values from inner query to qualify or
disqualify candidate row

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 6

6
Correlated Subqueries
• A correlated subquery is evaluated once for each row
processed by the parent statement

GET
candidate row from outer query

EXECUTE
inner query using candidate row value

USE
values from inner query to qualify or
disqualify candidate row

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 7

7
Correlated Subqueries
• The parent statement can be a SELECT, UPDATE, or
DELETE statement

GET
candidate row from outer query

EXECUTE
inner query using candidate row value

USE
values from inner query to qualify or
disqualify candidate row

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 8

8
Correlated Subquery Example
• Whose salary is higher than the average salary of their
department?
• To answer that question, we need to write a correlated
subquery
• Correlated subqueries are used for row-by-row
processing FIRST_NAME LAST_NAME SALARY
SELECT o.first_name,o.last_name, Steven King 24000
o.salary Shelley Higgins 12000
FROM employees o Eleni Zlotkey 10500
WHERE o.salary >
Ellen Abel 11000
(SELECT AVG(i.salary)
FROM employees i Kevin Mourgos 5800
WHERE i.department_id = Alexander Hunold 9000
o.department_id); Michael Hartstein 13000

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 9

9
 Correlated Subquery Example
• Each subquery is executed once for every row of the
outer query
• With a normal subquery, the inner SELECT query runs
first and executes once, returning values to be used by
the outer query
SELECT o.first_name, FIRST_NAME LAST_NAME SALARY
o.last_name, o.salary Steven King 24000
FROM employees o Shelley Higgins 12000
WHERE o.salary > Eleni Zlotkey 10500
(SELECT AVG(i.salary) Ellen Abel 11000
FROM employees i Kevin Mourgos 5800
WHERE i.department_id = Alexander Hunold 9000
o.department_id); Michael Hartstein 13000

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 10

A row is fetched from the employees table.

The average salary of the department_id of that employee is calculated by the subquery.
The outer query compares the employee's salary with the average for their department, and if their salary
is greater than the average for their department, the row is returned.
The next row is then fetched and the process repeats.

10
 Correlated Subquery Example
• A correlated subquery, however, executes once for
each row considered by the outer query
• In other words, the inner query is driven by the outer
query
• The correlated subquery in this example is marked in
red FIRST_NAME LAST_NAME SALARY
SELECT o.first_name, Steven King 24000
o.last_name, o.salary Shelley Higgins 12000
FROM employees o Eleni Zlotkey 10500
WHERE o.salary >
Ellen Abel 11000
(SELECT AVG(i.salary)
FROM employees i Kevin Mourgos 5800
WHERE i.department_id = Alexander Hunold 9000
o.department_id); Michael Hartstein 13000

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 11

Correlated subqueries executes completely differently to non-correlated subqueries, in as much as they are
driven by the outer query. So the outer query is executed, the first row returned and for THAT row the
inner query is executed.

11
EXISTS & NOT EXISTS in Subqueries
• EXISTS, and its opposite NOT EXISTS, are two clauses
that can be used when testing for matches in
subqueries
• EXISTS tests for a TRUE, or a matching result in the
subquery
• To answer the question: "Which employees are not
managers?"
−You first have to ask, "Who are the managers?"
−And then ask, "Who does NOT EXIST on the managers list?"

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 12

12
EXISTS & NOT EXISTS in Subqueries
• In this example, the subquery is selecting the
employees that are managers
• The outer query then returns the rows from the
employee table that do NOT EXIST in the subquery
Not a Manager
Whalen
SELECT last_name AS "Not a Manager" Gietz
FROM employees emp Abel
WHERE NOT EXISTS Taylor
(SELECT * Grant
FROM employees mgr Rajs
WHERE mgr.manager_id = emp.employee_id); Davies
Matos
Vargas
Ernst
…

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 13

13
EXISTS & NOT EXISTS in Subqueries
• If the same query is executed with a NOT IN instead of
NOT EXISTS, the result is very different
• The result of this query suggests there are no
employees who are also not managers, so all
employees are managers, which we already know is
not true
SELECT last_name AS "Not a Manager"
FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT mgr.manager_id
FROM employees mgr);
no data found

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 14

14
EXISTS & NOT EXISTS in Subqueries
• The cause of the strange result is due to the NULL
value returned by the subquery
• One of the rows in the employees table does not have
a manager, and this makes the entire result wrong
• Subqueries can return three values: TRUE, FALSE, and
UNKNOWN
• A NULL in the subquery result set will return an
UNKNOWN, which Oracle cannot evaluate, so it
doesn't SELECT last_name AS "Not a Manager"
FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT mgr.manager_id
FROM employees mgr); no data found
DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 15

15
 EXISTS & NOT EXISTS in Subqueries
• BEWARE of NULLS in subqueries when using IN or NOT
IN
• If you are unsure whether or not a subquery will
include a null value, either eliminate the null by using
IS NOT NULL in a WHERE clause
• For example: WHERE emp.manager_id IS NOT NULL
or use NOT EXISTS to be safe

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 16

NOT can be used with IN, ANY and ALL.

16
WITH Clause
• If you have to write a very complex query with joins
and aggregations used many times, you can write the
different parts of the statement as query blocks and
then use those same query blocks in a SELECT
statement
• Oracle allows you to write named subqueries in one
single statement, as long as you start your statement
with the keyword WITH
• The WITH clause retrieves the results of one or more
query blocks and stores those results for the user who
runs the query

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 17

17
WITH Clause
• The WITH clause improves performance
• The WITH clause makes the query easier to read
• The syntax for the WITH clause is as follows:
WITH subquery-name AS (subquery),
subquery-name AS (subquery)
SELECT column-list
FROM {table | subquery-name | view}
WHERE condition is true;

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 18

18
WITH Clause
• Write the query for the following requirement:
−Display a list of employee last names that are not managers
• To construct this query, you will first need to get a list
of manager_ids from the employee table, then return
the names of the employees whose employee id is not
on the managers list
• We can create a named subquery using the WITH
clause to retrieve the manager_id from the employees
table, then the outer query will return the employees
that do not appear on that list

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 19

19
WITH Clause
Not a manager
WITH managers AS
(SELECT DISTINCT manager_id Whalen
FROM employees Gietz
WHERE manager_id IS NOT NULL) Abel
Taylor
SELECT last_name AS "Not a manager" Grant
FROM employees Rajs
WHERE employee_id NOT IN
Davies
(SELECT *
FROM managers); Vargas
Ernst
…

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 20

20
Summary
• In this lesson, you should have learned how to:
−Identify when correlated subqueries are needed
−Construct and execute correlated subqueries
−Create a query using the EXISTS and NOT EXISTS operators to
test for returned rows from the subquery
−Construct and execute named subqueries using the WITH
clause

DP 10-4
Correlated Subqueries Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 21

21