## Minimising the number of table lookups (subqueries)


   SELECT EMP_NAME 
   FROM   EMP
   WHERE  EMP_CAT   = ( SELECT MAX(CATEGORY)
                        FROM   EMP_CATEGORIES )
   AND    SAL_RANGE = ( SELECT MAX(SAL_RANGE) 
                        FROM   EMP_CATEGORIES ) 
   AND    EMP_DEPT  =   0020;


## Specify the following instead:

   SELECT EMP_NAME 
   FROM   EMP
   WHERE (EMP_CAT, SAL_RANGE)
                    = ( SELECT MAX(CATEGORY), MAX(SAL_RANGE)
                        FROM   EMP_CATEGORIES )
   AND    EMP_DEPT  = 0020;



## For multi-column UPDATE statements, instead of specifying:

   UPDATE EMP 
   SET    EMP_CAT   = ( SELECT MAX(CATEGORY)
                        FROM   EMP_CATEGORIES ),
          SAL_RANGE = ( SELECT MAX(SAL_RANGE)
                        FROM   EMP_CATEGORIES )
   WHERE  EMP_DEPT  = 0020;


## try using:

   UPDATE EMP 
   SET   (EMP_CAT, SAL_RANGE)
                    = ( SELECT MAX(CATEGORY), MAX(SAL_RANGE)
                        FROM   EMP_CATEGORIES )
   WHERE  EMP_DEPT  = 0020;