views:

104

answers:

3

Hi

I have the following SQL:

SELECT * FROM Name 
INNER JOIN ( SELECT 2 AS item, NameInAddress.NameID as itemID, NameInAddress.AddressID
     FROM NameInAddress
     INNER JOIN Address ON Address.AddressID = NameInAddress.AddressID
     WHERE (Address.Country != 'UK')
) AS Items ON (Items.itemID = Name .Name ID)

I have been asked to remove the nested select and use INNER JOINS instead, as it will improve performance, but I'm struggling.

Using SQL Server 2008

Can anyone help?

Thanks!

+3  A: 

Your query is not correct as you're using Items.itemID while it's not in the subselect

I guess this is what you meant:

SELECT Name.*
FROM Name
INNER JOIN NameInAddress
ON Name.NameID = NameInAddress.NameID
INNER JOIN Address 
ON Address.AddressID = NameInAddress.AddressID
WHERE (Address.Country != 'UK')

EDIT: The exact translation of your query would start with a SELECT Name.*, 2 as Item, NameInAddress.NameID, NameInAddress.AddressID though

ybo
A: 
SELECT     2 AS Item, * 
FROM       Name 
INNER JOIN NameInAddress
ON         Name.NameID = NameInAddress.NameID
INNER JOIN Address 
ON         Address.AddressID = NameInAddress.AddressID
WHERE      Address.Country != 'UK'

PS: Don't use "*". This will increase performance too. :)

Maximilian Mayerl
Don't use "*" in a production environment - I'm tempted to say "ever" but there is always one case where you can justify doing something that is normally deemed wrong. Why? Well peformance issues aside, because changes to your schema may have unforseen consequences that will be easier to understand if you are explicit about the data you are retrieving.
Murph
Well, I couldn't find ANY case where it was justifiable to use "*". Can you tell me one?
Maximilian Mayerl
I had a case where an app was under active development, and new fields were constantly being added to tables, so the requirement from users for a table browsing screen form was explicitely "show me just everything that is there at this point in time".
ttarchala
... but then you're not in a production environment.
erikkallen
@Maximilian - I couldn't off hand, but putting an absolute like that in is just asking to get flamed (-:
Murph
+1  A: 

It is one of those long-lived myths that nested selects are slower than joins. It depends completely on what the nested select says. SQL is just a declarative language to tell what you want done, the database will transform it into completely different things. Both MSSQL and Oracle (and I suspect other major engines as well) are perfectly able to transform correlated subqueries and nested views into joins if it is beneficial (unless you do really complex things which would be very hard, if possible, to describe with normal joins.

erikkallen