how do I remove nested select statement

Question

Hi. I have a Name table with the columns NameID Name TypeID

With the following SQL

SELECT[NameID]
FROM[Name]
WHERE[TypeID] = @TypeID
AND NameID >= (SELECT MIN([NameID]) 
            FROM [Name] 
            WHERE [Name]='Billy' AND [TypeID]=@TypeID)

Ive been asked to convert this to an Inner Join without using any nested select but not sure how to. thanks for your help!

Answer 1

Originally I didn't think you needed a join at all,

;WITH n AS 
(
    SELECT
      NameID,
      rn = ROW_NUMBER() OVER (ORDER BY NameID)
    FROM [Name]
    WHERE TypeID = @TypeID
    AND [Name] = 'Billy'
)
SELECT NameID
    FROM n
    WHERE rn > 1;

Then again, maybe I do not have the requirements clear. What is the purpose of this query?

SELECT n1.NameID 
FROM [Name] AS n1
    INNER JOIN
(
    SELECT NameID = MIN(NameID) 
    FROM [Name]
    WHERE TypeID = @TypeID
    AND [Name] = 'Billy'
) AS n2
ON n1.NameID >= n2.NameID
WHERE n1.TypeID = @TypeID;

I agree with Lukas, I am not sure why the person who is telling you to change this thinks an inner join will be better than your original.

Answer 2

You could remove the nested part via: -

declare @NameID int

select @NameID = (SELECT MIN([NameID]) FROM [Name] WHERE [Name]='Billy' AND [TypeID]=@TypeID)

SELECT [NameID] FROM [Name] WHERE [TypeID] = @TypeID AND NameID >= @NameID

But as stated already, this does not provide any performance benefit as the subquery would only be evaluated once in your version, the same as in this.

Answer 3

Well, it looks like just moving the condition [Name]='Billy' should produce the same result for this specific query. So convert your original:

SELECT[NameID]
FROM[Name]
WHERE[TypeID] = @TypeID
AND NameID >= (SELECT MIN([NameID]) 
                   FROM [Name] 
                   WHERE [Name]='Billy' AND [TypeID]=@TypeID)

to:

SELECT[NameID]
FROM[Name]
WHERE[TypeID] = @TypeID
AND[Name]='Billy'