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  

  • Uncorrelated nested query:

    • 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 subquery: 

                   (SELECT essn         /* Can be executed by itself */     
	        FROM   dependent)

    can be executed without the using information from the outer query

  Correlated nested 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 subquery (inner query) is executed using some information from the outer 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