Select with in Select VS JOIN

Question

question

user-1117 (google) asked Apr 16, '10

Select with in Select VS JOIN

Hi Guys,

I need to know which one will give best performance in the below query

Select with in Select

select                     
p.ProductID,                    
p.ProductCode,                    
p.ProductName,                    
p.ProductStatus,                    
(select Name from QAdmin.TypeCode where TypeCodeID = p.ServiceTypeID)ProductServiceType,                    
(select Name from QAdmin.TypeCode where TypeCodeID = p.ResultSourceTypeID)ProductSourceType,                    
CreatedDate                    
from                     
QAdmin.Product p

JOIN

select                     
p.ProductID,                    
p.ProductCode,                    
p.ProductName,                    
p.ProductStatus,                    
(select Name from QAdmin.TypeCode where TypeCodeID = p.ServiceTypeID)ProductServiceType,                    
(select Name from QAdmin.TypeCode where TypeCodeID = p.ResultSourceTypeID)ProductSourceType,                    
CreatedDate                    
from                     
QAdmin.Product p                     
JOIN QAdmin.TypeCode st on st.TypeCodeID = p.ServiceTypeID                    
JOIN QAdmin.TypeCode rst on rst.TypeCodeID = p.ServiceTypeID

query performance

1

10 |1200

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

graz ♦ commented · Apr 16, 2010 at 10:59 AM

Sorry, they look identical except for the JOIN. Do they both give you the same query plan?

0 ·

Answer 1 · 2010-04-16T11:29:33Z

JohnFx answered Apr 16, '10

Why not try running them both in SSMS with statistics turn on and find get an authoritative answer?

That said, I'm betting the first one will be faster because the second one does more work. Seems a moot point since the two queries aren't equivalent.

10 |1200

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

Answer 2 · 2010-06-25T20:47:30Z

Jay D answered Jun 25, '10

The execution plan will help tremendously. Properly tuned indexes can make all the difference in the world.

10 |1200

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

question