Avoid using NOT IN - sql server and increase Performance and speed up your query

Posted by Saranpselvam under Sql Server category on 4/22/2014 | Points: 40 | Views : 1259

Post Code |

Search Codes |

Code Home

HI

For improving performance in your query we should not use "not in" . So how can we avoid not in?

consider a situation there are two tables t1,t2 both having a common column called "book Name". if you want to get book name from t1 but those name should not being in t2 table then we should writ our query like the following

Good practice
-----------------
SELECT t1.bookname
FROM t1
LEFT OUTER JOIN t2
ON t1.bookname= t2.bookname
WHERE t2.bookname IS NULL

Bad Practice
-----------------
select t1.bookname from t1 where t1.bookname not in(select t1.bookname from t2)

Thanks
saranpselvam@gmail.com
saran

Alert Moderator

Bookmark It

Comments or Responses

Posted by: vishalneeraj-24503 on: 4/23/2014 Level:Platinum | Status: [Member] [MVP] | Points: 10

Hi Saranpselvam,

Will your query work for all scenarios?If we do not use Not In in our query,then will your query work?
One more concern,in the where clause,you gave condition like t2.bookname IS NULL. What if t2.bookname contains any data then in that case your record count will be 0.But in case of Left Outer Join,1st or Left table records must be shown if right table does not contain any matching rows with left table.

Kindly clarify with real data with details.

Latest Code Snipptes