question

Dharmendra avatar image
Dharmendra asked

Which to use? 'join' or nested subqueries?

if i have five tables, and i have to change values in first table whose columns are depended on these four tables. which query is batter and good? this one....?

select A.id as folder_id, 
A.name as folder_name
from mst_folders A join mst_folder_requests B
on A.id = B.folder_id 
where B.user_id = @employeeId
and B.wfitem_id in 
(select item 
   from mst_wfitem_action_map 
   where item in 
    (Select ID 
       from mst_wfitems 
       where type = @WorkflowId and wfevent_id in 
            (select id 
               from mst_wfevents 
               where type_id = @EventTypeId 
               and user_id = @employeeId)
     )
   and actor = @actorId and state = 'P')

or this one?

select A.id folder_id, 
A.name folder_name 
from mst_folders A 
join mst_folder_requests B on A.id=B.folder_id 
join mst_wfitem_action_map C on B.wfitem_id = C.item 
join mst_wfitems D on C.item=D.id join mst_wfevents E on D.wfevent_id=E.id 
where B.user_id=@employeeId 
and D.type=@WorkflowId
and E.type_id=@EventTypeId 
and E.user_id=@employeeId 
and C.actor=@actorId and C.state='P'
t-sqljoins
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

Personally, visually, I prefer the join.

However, it's really a case of testing in your environment to see which one performs better for you.

You may also find that using correlated sub queries would provide a better plan.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

There is a time for each and it will depend on testing as Matt has said. The same as Matt, I prefer the join because it is easier to follow for me.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

I agree with Matt that I prefer JOINS, I find the syntax easier to pick up if I am new to the code as the 'nesting' suits my preference. At the end of the day, when its complex though you might have to go with which ever performs better. Test them both and go with the one that is least work for your server

3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

review the TSQL "SET STATISTICS TIME ON|OFF" and "SET STATISTICS IO ON|OFF" in books online and download SQL SERVER EXECUTION PLANS by Grant Fritchey from http://www.simple-talk.com/books/sql-books/sql-server-execution-plans/
3 Likes 3 ·
i think you are right, my sir was also saying to use JOIN, how can i measure performance of these two queries in sql server 2008?
0 Likes 0 ·
+1 here along with Matt.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.