Uncorrelated/correlated for-loops
- Uncorrelated
for-loop:
for (i = 0; i < N; i++) // Outer loop
{
for (j = 5; j < N; j++) // Inner loop
{
... // Execution behavior not dependent on other loop
}
}
|
- Correlated
for-loop:
for (i = 0; i < N; i++) // Outer loop
{
for (j = i; j < N-i; j++) // Inner loop
{
... // Execution behavior depends on other loop
}
}
|
|
Uncorrelated nested query
Meaning (= processing) of a
correlated query
- Consider the following
example
of a
correlated
nested query:
SELECT fname, lname, salary, dno
from employee A
where salary >= ALL (select salary
from employee B
where B.dno=A.dno)
|
- How to
evaluate/execute
a
correlated
nested
query:
FOR (each tuple a ∈ A (= employee) in the outer query) DO
{
Evaluate the tuple condition using the attribute values
found in the tuple a in the outer query;
}
|
I.e.:
- The
inner query
will use a
different value
for
A.dno
each time
|
|
Example evaluation (= processing)
of a correlated query
- Consider the following
correlated nested query:
SELECT fname, lname, salary, dno
from employee A
where salary >= ALL (select salary
from employee B
where B.dno=A.dno)
|
Sample input relation:
fname lname salary dno (other not relevant attr's omitted)
------ -------- --------- -----------
John Smith 30000.00 5
Frankl Wong 40000.00 5
Alicia Zelaya 25000.00 4
Jennif Wallace 43000.00 4
Ramesh Narayan 38000.00 5
Joyce English 25000.00 5
Ahmad Jabbar 25000.00 4
James Borg 55000.00 1
|
|
Example evaluation (= processing)
of a correlated query
SELECT fname, lname, salary, dno
from employee A
where salary >= ALL (select salary
from employee B
where B.dno=A.dno)
|
fname lname salary dno (other not relevant attr's omitted)
------ -------- --------- -----------
John Smith 30000.00 5 <-- tuple #1 in the outer relation
Frankl Wong 40000.00 5
Alicia Zelaya 25000.00 4
Jennif Wallace 43000.00 4
Ramesh Narayan 38000.00 5
Joyce English 25000.00 5
Ahmad Jabbar 25000.00 4
James Borg 55000.00 1
Outer tuple 1: A = John Smith 30000.00 5
WHERE salary >= ALL (select salary from employee B where B.dno=A.dno)
==> WHERE 30000.00 >= ALL (select salary from employee B where B.dno= 5 )
==> WHERE 30000.00 >= ALL (30000, 40000, 38000, 25000)
==> FALSE
|
|
Example evaluation (= processing)
of a correlated query
SELECT fname, lname, salary, dno
from employee A
where salary >= ALL (select salary
from employee B
where B.dno=A.dno)
|
fname lname salary dno (other not relevant attr's omitted)
------ -------- --------- -----------
John Smith 30000.00 5
Frankl Wong 40000.00 5 <-- tuple #2 in the outer relation
Alicia Zelaya 25000.00 4
Jennif Wallace 43000.00 4
Ramesh Narayan 38000.00 5
Joyce English 25000.00 5
Ahmad Jabbar 25000.00 4
James Borg 55000.00 1
Outer tuple 2: A = Frankl Wong 40000.00 5
WHERE salary >= ALL (select salary from employee B where B.dno=A.dno)
==> WHERE 40000.00 >= ALL (select salary from employee B where B.dno= 5 )
==> WHERE 40000.00 >= ALL (30000, 40000, 38000, 25000)
==> TRUE tuple #2 selected for output
|
|
Example evaluation (= processing)
of a correlated query
SELECT fname, lname, salary, dno
from employee A
where salary >= ALL (select salary
from employee B
where B.dno=A.dno)
|
fname lname salary dno (other not relevant attr's omitted)
------ -------- --------- -----------
John Smith 30000.00 5
Frankl Wong 40000.00 5
Alicia Zelaya 25000.00 4 <-- tuple #3 in the outer relation
Jennif Wallace 43000.00 4
Ramesh Narayan 38000.00 5
Joyce English 25000.00 5
Ahmad Jabbar 25000.00 4
James Borg 55000.00 1
Outer tuple 3: A = Alicia Zelaya 25000.00 4
WHERE salary >= ALL (select salary from employee B where B.dno=A.dno)
==> WHERE 25000.00 >= ALL (select salary from employee B where B.dno= 4 )
==> WHERE 25000.00 >= ALL (25000, 43000)
==> FALSE
|
|
Example evaluation (= processing)
of a correlated query
SELECT fname, lname, salary, dno
from employee A
where salary >= ALL (select salary
from employee B
where B.dno=A.dno)
|
fname lname salary dno (other not relevant attr's omitted)
------ -------- --------- -----------
John Smith 30000.00 5
Frankl Wong 40000.00 5
Alicia Zelaya 25000.00 4
Jennif Wallace 43000.00 4 <-- tuple #4 in the outer relation
Ramesh Narayan 38000.00 5
Joyce English 25000.00 5
Ahmad Jabbar 25000.00 4
James Borg 55000.00 1
Outer tuple 4: A = Jennif Wallace 43000.00 4
WHERE salary >= ALL (select salary from employee B where B.dno=A.dno)
==> WHERE 43000.00 >= ALL (select salary from employee B where B.dno= 4 )
==> WHERE 43000.00 >= ALL (25000, 43000)
==> TRUE tuple #4 selected for output And so on...
|
|
Example evaluation (= processing)
of a correlated query
SELECT fname, lname, salary, dno
from employee A
where salary >= ALL (select salary
from employee B
where B.dno=A.dno)
|
fname lname salary dno (other not relevant attr's omitted)
------ -------- --------- -----------
John Smith 30000.00 5
Frankl Wong 40000.00 5 <----
Alicia Zelaya 25000.00 4
Jennif Wallace 43000.00 4 <----
Ramesh Narayan 38000.00 5
Joyce English 25000.00 5
Ahmad Jabbar 25000.00 4
James Borg 55000.00 1 <----
Output:
fname lname salary dno
------ -------- --------- ----
Frankl Wong 40000.00 5 <--- Employee who has the
Jennif Wallace 43000.00 4 highest salary
James Borg 55000.00 1 in his/her department
|
|
❮
❯
