Who here has seen the movie "Noises Off"? Show of hands….oh dear, you're missing out. The movie is about a theater troupe doing a play, but interpersonal relations break down as they tour the country, and the play gets worse and worse with every show. It's bloody hilarious. One scene focuses around a plate of sardines that keeps disappearing, so the main character (Mrs. Clackett) has to keep making MORE sardines.
About 3/4 of the way through the movie, the play's gotten so bad – people are missing entrances and cues all over – that the sardines are sitting on the couch when they're supposed to be missing. The poor woman playing Mrs. Clackett says, "I've lost the sardines again!" [She sees them. She ad libs.] "No I haven't, I haven't forgotten the sardines. Well I'll just go make some more sardines to celebrate…"
I do have a point. Bet you can't wait to hear it, right?
Don't go get data that you already have. Our example today is thus: A set of three tables, each a child of the next. So the Seat table has a foreign key to (and is a child of) the Room table, and the Room table has a FK to the Theater table. Seat -> Room -> Theater.
SELECT SeatID
FROM Seat
INNER JOIN Room ON Room.RoomID = Seat.RoomID
INNER JOIN Theater ON Room.TheaterID = Theater.TheaterID
WHERE Theater.TheaterID = @TheaterID
I expect you can see the problem here. We want to ultimately limit our result set based on the variable @TheaterID, yes. But we don't have to go all the way to the Theater table to do that! TheaterID is stored in the Room table. Let's save ourselves a few hundred logical reads, my friends, and do this instead:
SELECT SeatID
FROM Seat
INNER JOIN Room ON Room.RoomID = Seat.RoomID
WHERE Room.TheaterID = @TheaterID
Now isn't that nice? Let's all have a plate of…SARDINES!