Yaakov Ellis Yaakov Ellis - 1 year ago 65
SQL Question

Is there any reason not to join Foreign Key to Foreign Key?

I have the following tables:


  • PK_FinancialID

  • FK_SchoolID


  • PK_SchoolID


  • PK_ClassID

  • FK_SchoolID

  • ClassName

Both Class and Financial have Foreign Key relationships to School. I want to make a query that would show all classes that are related to Financial rows that meet certain criteria.

Initially I think to construct the query as follows:

Select Class.ClassName
From Class
Join School on Class.FK_SchoolID = School.PK_SchoolID
Join Financial on Financial.FK_SchoolID = Schol.PK_SchoolID
Where Financial ... -- define criteria

However, since both Financial and Class are joined on the PK_SchoolID column, it should be possible to rewrite the query as follows (cutting out the School table and joining Class and Financial directly):

Select Class.ClassName
From Class
Join Financial on Financial.FK_SchoolID = Class.FK_SchoolID
Where Financial ... -- define criteria

Which approach is preferable from a sql perspective? Would including the School table make performance better because the actual PK record is referenced (and thus a Clustered Index can be referenced)? Or does that not really matter? Anything that I am missing?

Platform: Sql Server 2005. All tables have their PK and FK columns properly declared and defined.

Answer Source

If you don't need School, don't join School. If you wan't this query to run fast, create index on FK_SchoolID of Financial table. It looks as if you have n-1-1 relation between Class-School-Financial, so you should even create unique index on Financial. You shouldn't (in most cases) add additional tables to make query faster, just optimize used.


If you select only ClassName, maybe what you need is:

Select Class.ClassName
From Class
Where Exists 
    (select * from Financial 
    where (Financial.FK_SchoolID = Class.FK_SchoolID) and (...))

It may be faster than other solutions and more understandable.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download