Performance of inner join query for certain condition

I have one question about inner join query in SQL Server 2008. Is there any situation where NOT to use inner joins.
I have one scenario please tell me that here inner join is fruitful or not.

I have 2 tables

• Table1 Columns: T1Id, Code and Name1, (Here Code is unique column)

• Table2 Columns: T2Id, T1id(reference from Table 1), Name2, Type

Now there can be millions of records in both table. Now user pass the Code and I have to search the Name2 3 times in same procedure for diff condition.
I can do it with 2 scenario.

Scenario 1

I can store T1Id in temp variable

Declare @Id Int
Select @Id=Top 1 T1Id from Table1 Where Code='ABC'

Step 2: I will write all my queries

Select Name2 From Table2 Where T1id=@Id and Type='A'
Select Name2 From Table2 Where T1id=@Id and Type='B'
Select Name2 From Table2 Where T1id=@Id and Type='C'

Scenario 2:

Select Name2 
from Table2 Inner join Table1 on Table1.T1Id = Table2.T1Id
Where Table2.Type='A' and Table1.Code='ABC'

Select Name2 
From Table2 Inner join Table1 on Table1.T1Id = Table2.T1Id
Where Table2.Type='B' and Table1.Code='ABC'

Select Name2 
From Table2 Inner join Table1 on Table1.T1Id = Table2.T1Id
Where Table2.Type='C' and Table1.Code='ABC'

Please tell me which one is good to use. Both tables can have more than millions records in future.

Run one query, make sure you have good indexes

Select Name2 
From Table2 Inner join Table1 on Table1.T1ITable2.T1Id
Where Table2.Type IN ('A', 'B', 'C') and Table1.Code='ABC'

A graphical query plan will show where you need indexes

A million rows isn't much and well within capability of SQL Server

StackExchange Database Administrators Q#7461, answer score: 3