CODEWITHSUNDEEP
sqlsql-serverdatabasejoin
Yaakov Ellis
Yaakov Ellis

Reputation: 41550

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

I have the following tables:

Financial:

School:

Class:

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.

Upvotes: 1

Views: 298

Answers (5)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

Seems to me that both of your examples are wrong. The fact that a school is listed as financial and that the school offers classes, does not mean that a specific class is a financial class -- it can be an art class from an another course. Seems that this is a weakness of the whole model, nothing to do with your SQL technique -- or maybe I do not understand the underlying model and all special constraints you may have. However, here is an example of a similar model:

  • One school can offer many courses; a course can be offered by several schools.
  • Each school may have specific name and description for a "generic" course.
  • One certificate requires several courses; a course may be required by many certificates.

certification_model_01

Upvotes: 1

LukLed
LukLed

Reputation: 31862

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.

EDIT

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.

Upvotes: 3

Himadri
Himadri

Reputation: 8876

Try the following:

Select Class.ClassName
From Class
Inner Join Financial on Financial.FK_SchoolID = Class.FK_SchoolID
Where Financial....yourcriteria

No need to join school table.

Upvotes: 1

Guffa
Guffa

Reputation: 700650

Yes, the index most definitely affects the performance.

Just add an index for the FK_SchoolID in the Financial table so that there is an index that the query can use.

Note that adding another index gives a slight performance hit when you add or delete records in the table. This is often outweighed by the big performance gain you get when querying the table, but it's the reason why you should be somewhat restrictive with adding indexes and don't just add indexes to all fields.

Upvotes: 2

Franz
Franz

Reputation: 11553

I'd say you're fine to leave out the actual school table. Don't see anything wrong with that.

As far as performance goes: I'm not really sure, but I'd say it would be faster because you have one less table to join - but I'm not an expert in that area...

Upvotes: 0

Related Questions