SQL点滴之性能优化其实没有那么神秘(1)(3)

5、 子查询的用法

子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。

子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。相关子查询可以用IN、NOT IN、EXISTS、NOT EXISTS引入。

关于相关子查询，应该注意：

A、NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法。比如：

SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE PUB_ID NOT IN 
   (SELECT PUB_ID 
   FROM TITLES 
   WHERE TYPE = 'BUSINESS') 
            可以改写成： 
 
SELECT A.PUB_NAME 
FROM PUBLISHERS A LEFT JOIN TITLES B 
ON        B.TYPE = 'BUSINESS' AND 
          A.PUB_ID=B.PUB_ID 
WHERE B.PUB_ID IS NULL 
  
 
SELECT TITLE 
FROM TITLES 
WHERE NOT EXISTS 
   (SELECT TITLE_ID 
   FROM SALES 
   WHERE TITLE_ID = TITLES.TITLE_ID) 
 
可以改写成： 
 
SELECT TITLE 
FROM TITLES LEFT JOIN SALES 
ON SALES.TITLE_ID = TITLES.TITLE_ID 
WHERE SALES.TITLE_ID IS NULL 

B、 如果保证子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN 代替。比如：

SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE PUB_ID IN 
   (SELECT PUB_ID 
   FROM TITLES 
   WHERE TYPE = 'BUSINESS') 
 
可以改写成： 
 
SELECT DISTINCT A.PUB_NAME 
FROM PUBLISHERS A INNER JOIN TITLES B 
ON        B.TYPE = 'BUSINESS' AND 
          A.PUB_ID=B.PUB_ID 

C、 IN的相关子查询用EXISTS代替，比如

SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE PUB_ID IN 
   (SELECT PUB_ID 
   FROM TITLES 
   WHERE TYPE = 'BUSINESS') 
 
可以用下面语句代替： 
 
SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE EXISTS 
   (SELECT 1 
   FROM TITLES 
   WHERE TYPE = 'BUSINESS' AND 
   PUB_ID= PUBLISHERS.PUB_ID) 

D、不要用COUNT(*)的子查询判断是否存在记录，最好用LEFT JOIN或者EXISTS，比如有人写这样的语句：

SELECT JOB_DESC FROM JOBS 
WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0 
 
应该改成： 
 
SELECT JOBS.JOB_DESC FROM JOBS LEFT JOIN EMPLOYEE  
ON EMPLOYEE.JOB_ID=JOBS.JOB_ID 
WHERE EMPLOYEE.EMP_ID IS NULL 
  
 
SELECT JOB_DESC FROM JOBS 
WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)<>0 
 
应该改成： 
 
SELECT JOB_DESC FROM JOBS 
WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)