---

Correlated nested queries

---

• Corrected and uncorrelated loops in programming languages
  • Uncorrelated for-loop:

    for (i = 0; i < N; i++) // Outer loop { for (j = 5; j < N; j++) // Inner loop { ... } }

    The inner loop is independent from the outer loop

    In fact:

    The inner loop can be execute as a stand-alone statement: for (j = 5; j < N; j++) // No syntax error !!! { ... }

    ---

    ---

  • Correlated for-loop:

    for (i = 0; i < N; i++) // Outer loop { for (j = i; j < N-i; j++) // Inner loop { ... } }

    The inner loop is dependent on the outer loop through the variable i.

  ---

  ---

  ---

  ---

• Correlated nested queries
  • So far, the nested queries used are uncorrelated nested queries:

    The inner (sub)query is a stand-alone query that can be executed independently from the outer query Example: SELECT fname, lname FROM employee WHERE ssn NOT IN (SELECT essn /* Can be executed by itself */ FROM dependent)

    The inner query is independent from the outer query.

    ---

    ---

  • Correlated nested query:

    Correlated nested query = a nested query where the inner query (i.e., the query inside the WHERE clause) uses one or more attributes from relation(s) specfied in the outer query.

  • Schematically:

    The attribute attr4 of the relation R1 in the outer query is used in the inner query

  ---

  ---

  ---

  ---

• Meaning of a nested query
  • Consider the following example of a 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 execute a correlated 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; }

  ---

  ---

  ---

  ---

• Example of a correlated nested 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)

    ---

    ---

  • The execution of this correlated nested query is as follows:

    Suppose the employee relation contains the following tuples: 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 Let us execute the following correlated query: SELECT fname, lname, salary, dno from employee A // Check each tuple in cartesian prod ! where salary >= ALL (select salary // Note: condition on a tuple from employee B where B.dno=A.dno) The sub-query depends on the current tuple !! Execution: 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 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 (I.e.: OUTPUT the outer tuple !!!) 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 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 (I.e.: OUTPUT the outer tuple !!!) AND SO ON... Output: fname lname salary dno ------ -------- --------- ----------- Frankl Wong 40000.00 5 Jennif Wallace 43000.00 4 James Borg 55000.00 1

  • Conclusion:

    The query: SELECT fname, lname, salary, dno from employee A where salary >= ALL (select salary from employee B where B.dno=A.dno) will output: The fname, lname, salary, dno of employees whose salary is greater than or equal to the salary of all employees in his/her own department !!!