SQL Server Performance Forum – Threads Archive

Converting nested IN to Joins

Hi, I was wondering if you guys had any tips for me. I want to convert the following code, which uses nested IN statements, to a faster running statement that uses JOINS. It seems relatively easy, but my brain is fried right now. I know job_id is more selective then the other values, so that would have to be in the beginning part of the statement I think. Thanks for the help in advance. select * from user_master where user_id IN
(select user_id from user_role_job where job_id=’000000000102′ and user_id IN
(select user_id from user_location where location_id IN
(select location_id from gen_location where location_id=’000009′))) and discontinued_date IS NULL

---

SELECT * FROM USER_MASTER UM INNER JOIIN USER_ROLE_JOB URJ ON UM.USER_ID = URJ.USER_ID
INNER JOIN USER_LOCAION UL INNER JOIN UL.USER_ID = URJ.USER_ID
INNER JOIN GEN_LOCATION GL ON GL.LOCATION_ID = UL.LOCATIONID
WHERE URL.JOB_ID = 00000000102 AND UM.DISCONTINUED IS NULL —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

---

Never use simply * for the column list when joining multiple tables. You may also need to use SELECT DISTINCT instead of just SELECT, which is why the version with subqueries may perform better. Another alternative for this is to use EXISTS subqueries in the WHERE clause.

---

read this before you start changing things: http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx _______________________________________________
Causing trouble since 1980
blog:http://weblogs.sqlteam.com/mladenp

---

I use existence checks wherever possible. They are a true/false which returns as soon as the check passes or fails.

---

]]>