Some thoughts of subquery efficiency

from http://blog.csdn.net/haiwer/article/details/2826881

1. Use LEFT JOIN instead of NOT IN, NOT EXISTS

Change
SELECT PUB_NAME
FROM PUBLISHERS
WHERE PUB_ID NOT IN
   (SELECT PUB_ID
   FROM TITLES
   WHERE TYPE = 'BUSINESS')

TO
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

Change
SELECT TITLE
FROM TITLES
WHERE NOT EXISTS
   (SELECT TITLE_ID
   FROM SALES
   WHERE TITLE_ID = TITLES.TITLE_ID)

TO
SELECT TITLE
FROM TITLES LEFT JOIN SALES
ON SALES.TITLE_ID = TITLES.TITLE_ID
WHERE SALES.TITLE_ID IS NULL


2. Use INNER JOIN instead of IN, EXISTS if there are no duplicates in subquery

Change
SELECT PUB_NAME
FROM PUBLISHERS
WHERE PUB_ID IN
   (SELECT PUB_ID
   FROM TITLES
   WHERE TYPE = 'BUSINESS')

To
SELECT DISTINCT A.PUB_NAME
FROM PUBLISHERS A INNER JOIN TITLES B
ON        B.TYPE = 'BUSINESS' AND
          A.PUB_ID=B. PUB_ID


3. Use EXISTS instead of IN in subquery

Change
SELECT PUB_NAME
FROM PUBLISHERS
WHERE PUB_ID IN
   (SELECT PUB_ID
   FROM TITLES
   WHERE TYPE = 'BUSINESS')

To
SELECT PUB_NAME
FROM PUBLISHERS
WHERE EXISTS
   (SELECT 1
   FROM TITLES
   WHERE TYPE = 'BUSINESS' AND
   PUB_ID= PUBLISHERS.PUB_ID)