# Correlated Subqueries

## 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.

## 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.”
• 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.

## Correlated Subqueries

• The Oracle server performs a correlated subquery when the subquery references a column from a table referred to in the parent statement.
• A correlated subquery is evaluated once for each row processed by the parent statement.

### 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.
• 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.

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.

• 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 the example above is marked in red.

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.

## 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?”
• 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.
• 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.
• 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.
• 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.

NOT can be used with IN, ANY and ALL.

## 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.
• 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;``````
• 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

## 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.