Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Do you need to run 3 queries? –  gbn Nov 1 '11 at 9:57
add comment

1 Answer

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

share|improve this answer
    
As @gbn said, good indexing will make either solution work well. Just use the simplest one (the join). –  Marian Nov 1 '11 at 13:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.