I also concur. The joins should take advantage of indexes whereas the subquery may not. Even though it is a small table, I still think it matters.
Dave -----Original Message----- From: Michael Dinowitz [mdino...@houseoffusion.com<mailto:mdino...@houseoffusion.com>] Sent: Thursday, December 05, 2013 08:04 PM Eastern Standard Time To: sql Subject: subquery vs join I've got an applicant table that including an ethnicity id. I need to convert the id into a text value from a separate ethnicity lookup table (with less than 10 entries). I can: 1. Do a subquery against the ethnicity table in the select part of the query and alias the return value 2. Do a join to the ethnicity table My tests on SQL 2008 show that the subquery can run into some wait time on the server and seems to be slower and less efficient. This leads me to think that using a join is the best idea. Would this be the general consensus? Somehow joining to a lookup table feels wrong, but that might just be the caffeine. :) Thanks ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3520 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
