sql 最小化表查询次(col1,col2) = (select col1,col2 from t)

[size=medium][color=red][b]最小化表查询次数[/b][/color][/size]

-->在含有子查询的SQL语句中，要特别注意减少对表的查询                                                                             
-->低效:                                                           
SELECT *                                                                                      
FROM   employees                                                                                   
WHERE  department_id = (SELECT department_id                                                       
                        FROM   departments                                                     
                        WHERE  department_name = 'Marketing')                                   
       AND manager_id = (SELECT manager_id                                                       
                         FROM   departments                                                     
                         WHERE  department_name = 'Marketing');                                  
-->高效:                                                                
SELECT *                                                                                    
FROM   employees                                                                             
WHERE  ( department_id, manager_id ) = (SELECT department_id, manager_id                                  
                                        FROM   departments                                                   
                                        WHERE  department_name = 'Marketing')                   

-->类似更新多列的情形                
-->低效:                     
UPDATE employees                                                                                  
SET    job_id = ( SELECT MAX( job_id ) FROM jobs ), salary = ( SELECT AVG( min_salary ) FROM jobs )           
WHERE  department_id = 10;                                                                    

-->高效:                  
UPDATE employees           
SET    ( job_id, salary ) = ( SELECT MAX( job_id ), AVG( min_salary ) FROM jobs )       
WHERE  department_id = 10;