https://myplsqltutorial.blogspot.

com/2017/04/how-correlated-subquery-works-in-
oracle.html

How Correlated Subquery works in Oracle

Let's assume we have following table in our  schema.

Let’s check how correlated sub query works using above table.

Here I’m going to write a query to get 2nd highest salary.

Select *from emp_chk e1 where 2= (Select Count(distinct(sal)) From

Emp_Chk e2 Where e2.sal >=e1.sal)

As we know definition of correlated sub query is ,main query would execute

first and for that result set ,inner query would execute once per each row (of
main query output).

here inner query is dependent on main query.

 
Let’s go through in practical for above query:-
 
As per definition we will get all records from emp_chk table but here we
need to concentrate on sal column because that column used in join
condition.
So 67760,32000,25000,34345 are values for sal columns.
1st step: Pass 67760 in inner query.
Select *from emp_chk e1 where 2= (Select Count(distinct(sal)) From
Emp_Chk e2 Where e2.sal >= 67760)

Here I have replaced e1.sal with 67760 because that is 1st value which
would come and check that query condition. So if you see there, we would
get count only one as we have only 1 record which is >=67760.

It means where condition doesn’t satisfy (i.e. where 2=1).so we don’t get
any result.

2nd step: Pass 32000 in inner query

Select *from emp_chk e1 where 2= (Select Count(distinct(sal)) From
Emp_Chk e2 Where e2.sal >= 32000)

Here we ill get count 3 as we have 3 records which are greater than or equal
to 32000 so this one also doesn’t satisfy condition ( i.e. where 2=3). So we
don’t get any result.

3rd step: Pass 25000 in inner query

Select *from emp_chk e1 where 2= (Select Count(distinct(sal)) From
Emp_Chk e2 Where e2.sal >= 25000)

Here we ill get count 4 as we have 4 records which are greater than or equal
to 25000 so this one also doesn’t satisfy condition ( i.e. where 2=4). So we
don’t get any result.

4th step: Pass 34345 in inner query

Select *from emp_chk e1 where 2= (Select Count(distinct(sal)) From
Emp_Chk e2 Where e2.sal >= 34345)

Here we ill get count 2 as we have 2 records which are greater than or equal
to 34345 so finally our where condition got satisfied (i.e. where 2=2) for sal
value 34345.So oracle would return this record as output.

In this way we can find out nth highest salary using correlated subquery.
https://www.w3resource.com/sql/subqueries/corelated-subqueries-using-aliases.php

SQL Correlated Subqueries

Last update on February 26 2020 08:07:42 (UTC/GMT +8 hours)

Correlated Subqueries

SQL Correlated Subqueries are used to select data from a table referenced in the outer query.
The subquery is known as a correlated because the subquery is related to the outer query. In this
type of queries, a table alias (also called a correlation name) must be used to specify which table
reference is to be used.

The alias is the pet name of a table which is brought about by putting directly after the table
name in the FROM clause. This is suitable when anybody wants to obtain information from two
separate tables.

Example: SQL Correlated Subqueries

The following correlated subqueries retrive ord_num, ord_amount, cust_code and agent_code
from the table orders ( 'a' and 'b' are the aliases of orders and agents table) with following
conditions -

the agent_code of orders table must be the same agent_code of agents table and agent_name of
agents table must be Alex,

the following SQL statement can be used:

Sample table: orders

Sample table: agents

SQL Code:

SELECT a.ord_num,a.ord_amount,a.cust_code,a.agent_code
FROM orders a
WHERE a.agent_code=(
SELECT b.agent_code
FROM agents b WHERE b.agent_name='Alex');
Copy

Output:

ORD_NUM ORD_AMOUNT CUST_CODE AGENT_CODE

---------- ---------- ---------- ----------
200127 2500 C00015 A003
200100 1000 C00015 A003

The inner of the above query returns the 'agent_code' A003.

The simplified form of above code is:

SQL Code:

SELECT a.ord_num,a.ord_amount,a.cust_code,a.agent_code
FROM orders a
WHERE a.agent_code='A003';
Copy

Pictorical Presentation:
Using EXISTS with a Correlated Subquery

We have already used the EXISTS operator to check the existence of a result of a subquery.
EXISTS operator can be used in correlated subqueries also. Using EXISTS the following query
display the employee_id, manager_id, first_name and last_name of those employees who
manage other employees.

SQL Code:

SELECT employee_id, manager_id, first_name, last_name

FROM employees a
WHERE EXISTS
(SELECT employee_id
FROM employees b
WHERE b.manager_id = a.employee_id)
Copy

Sample table: employees

Output:

EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME

----------- ---------- -------------------- ---------------
100 Steven King
101 100 Neena Kochhar
102 100 Lex De Haan
103 102 Alexander Hunold
108 101 Nancy Greenberg
114 100 Den Raphaely
120 100 Matthew Weiss
121 100 Adam Fripp
122 100 Payam Kaufling
123 100 Shanta Vollman
124 100 Kevin Mourgos
145 100 John Russell
146 100 Karen Partners
147 100 Alberto Errazuriz
148 100 Gerald Cambrault
149 100 Eleni Zlotkey
201 100 Michael Hartstein
205 101 Shelley Higgins

Pictorial Presentation:
Using NOT EXISTS with a Correlated Subquery

NOT EXISTS is logically opposite of EXISTS operator. NOT EXISTS is used when we need to
check if rows do not exist in the results returned by a subquery. Using NOT EXISTS the
following query display the employee_id, manager_id, first_name and last_name of those
employees who have no manager status. This query is opposite to the previous one.

SQL Code:

SELECT employee_id, manager_id, first_name, last_name

FROM employees a
WHERE NOT EXISTS
(SELECT employee_id
FROM employees b
WHERE b.manager_id = a.employee_id);
Copy

Sample table: employees

Output:

EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME

----------- ---------- -------------------- --------------
104 103 Bruce Ernst
105 103 David Austin
106 103 Valli Pataballa
107 103 Diana Lorentz
109 108 Daniel Faviet
110 108 John Chen
111 108 Ismael Sciarra
112 108 Jose Manuel Urman
113 108 Luis Popp
115 114 Alexander Khoo
116 114 Shelli Baida
117 114 Sigal Tobias
118 114 Guy Himuro
119 114 Karen Colmenares
125 120 Julia Nayer
126 120 Irene Mikkilineni
127 120 James Landry
128 120 Steven Markle
129 121 Laura Bissot
130 121 Mozhe Atkinson
131 121 James Marlow
........
.......

Pictorial Presentation:
https://www.geeksforgeeks.org/sql-correlated-subqueries/
SQL Correlated Subqueries
Last Updated: 06-02-2018

Correlated subqueries are used for row-by-row processing. Each subquery is executed once for
every row of the outer query.

A correlated subquery is evaluated once for each row processed by the parent statement. The
parent statement can be a SELECT, UPDATE, or DELETE statement.

SELECT column1, column2, ....

FROM table1 outer
WHERE column1 operator
(SELECT column1, column2
 FROM table2
WHERE expr1 =
outer.expr2);

A correlated subquery is one way of reading every row in a table and comparing values in each
row against related data. It is used whenever a subquery must return a different result or set of
results for each candidate row considered by the main query. In other words, you can use a
correlated subquery to answer a multipart question whose answer depends on the value in each
row processed by the parent statement.

Nested Subqueries Versus Correlated Subqueries :

With a normal nested subquery, the inner SELECT query runs first and executes once, returning
values to be used by the main query. A correlated subquery, however, executes once for each
candidate row considered by the outer query. In other words, the inner query is driven by the
outer query.
NOTE : You can also use the ANY and ALL operator in a correlated subquery.
EXAMPLE of Correlated Subqueries : Find all the employees who earn more than the average
salary in their department.

SELECT last_name, salary, department_id

FROM employees outer
WHERE salary >
(SELECT AVG(salary)
FROM employees
WHERE department_id =
outer.department_id);

Other use of correlation are in UPDATE and DELETE

CORRELATED UPDATE :
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column =
alias2.column);

Use a correlated subquery to update rows in one table based on rows from another table.

CORRELATED DELETE :
DELETE FROM table1 alias1
WHERE column1 operator
(SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
Use a correlated subquery to delete rows in one table based on the rows from another table.

Using the EXISTS Operator :

The EXISTS operator tests for existence of rows in the results set of the subquery. If a subquery
row value is found the condition is flagged TRUE and the search does not continue in the inner
query, and if it is not found then the condition is flagged FALSE and the search continues in the
inner query.
EXAMPLE of using EXIST operator :
Find employees who have at least one person reporting to them.

SELECT employee_id, last_name, job_id, department_id

FROM employees outer
WHERE EXISTS ( SELECT ’X’
FROM employees
WHERE manager_id =
outer.employee_id);

OUTPUT :

EXAMPLE of using NOT EXIST operator :

Find all departments that do not have any employees.

SELECT department_id, department_name

FROM departments d
WHERE NOT EXISTS (SELECT ’X’
FROM employees
WHERE department_id
= d.department_id);
OUTPUT :