Analogy: scopes in programming languages  

  • Scopes in programming languages: 

       public static void main(String[] args)           
   {  // Outer scope 
      .....
      {  
         .....
         // Inner scope  
      } 
   }

  • Scoping rules in programming languages: 

       public static void main(String[] args)
   {
      int i = 4;
      {  // Inner scope                                                  
         double x = 3.14;                                                
                                                                         
         System.out.println("i = " + i + ", x = " + x);  // We can use i 
      }                                                                  
      System.out.println("i = " + i + ", x = " + x); // Error - can't use x
   }

  Scopes in SQL programs  

  • Each nested query constitutes a new inner scope: 

       SELECT ...               // Outer scope
    FROM   ...
    WHERE  ......
            (SELECT ...
             FROM   ...     // Inner scope           
             WHERE  ... )

  • Scoping rule in SQL:

    • The names of relations and their attribute name in an outer query are visible (= usable) in the inner query

  Example Scoping Rule in SQL  

  • Example of nested query: 

         SELECT  attr. names from R1 and/or R2   /* Outer query */    
     FROM    R1, R2
     WHERE   attr.names from R1 and/or R2 
              ....
              /* inner query */
              ( SELECT   attr. names from R1, R2, R3 and/or R4    
                FROM     R3, R4
		WHERE    attr. names from R1, R2, R3 and/or R4
	      )

  • The attributes of R1 and R2 are visible (= accessible) in the inner query

  • But the attributes of R3 and R4 are not visible (= not accessible) in the outer query

  Association rules for attribute names  

  • How to associate a relation to an attribute name:

    • An attribute name specified in the outer query is always associated with a relation in the outer query

    • An attribute name specified in the inner query is associated with the nearest relation (scope-wise) that contains the attribute name

  • Question:   which relation is associated with the attribute  x : 

         SELECT  ....                      /* Outer query */    
     FROM    R1, R2
     WHERE   ....
             ....
              ( SELECT   x             /* inner query */
                FROM     R3, R4
		WHERE    x = ....
	      )

    We will consider all the different cases next

  Association rules for attribute names   

         SELECT  ....                      /* Outer query */    
     FROM    R1, R2
     WHERE   ....
             ....
              ( SELECT   x             /* inner query */
                FROM     R3, R4
		WHERE    x = ....
	      )

  • If relation R3 (or R4) contains the attribute name x, then:

    • the attribute name x in the inner query belongs to relation R3 (or R4)

  Association rules for attribute names   

         SELECT  ....                      /* Outer query */    
     FROM    R1, R2
     WHERE   ....
             ....
              ( SELECT   x             /* inner query */
                FROM     R3, R4
		WHERE    x = ....
	      )

  • If both relations R3 and R4 contain the attribute name x, then:

    • the attribute name x is ambiguous and you must qualify it with R3 or R4

  Association rules for attribute names   

         SELECT  ....                      /* Outer query */    
     FROM    R1, R2
     WHERE   ....
             ....
              ( SELECT   x             /* inner query */
                FROM     R3, R4
		WHERE    x = ....
	      )

  • If relations R3 and R4 do not contain the attribute name x, then:

    • the attribute name x in the inner query refers to an attribute in R1 or R2