SQL Question: Does the order of the WHERE/INNER JOIN clause when interlinking table matter?

Question

Exam Question (AQA A-level Computer Science):
[Primary keys shown by asterisks]

Athlete(*AthleteID*, Surname, Forename, DateOfBirth, Gender, TeamName)
EventType(*EventTypeID*, Gender, Distance, AgeGroup)
Fixture(*FixtureID*, FixtureDate, LocationName)
EventAtFixture(*FixtureID*, *EventTypeID*)
EventEntry(*FixtureID*, *EventTypeID*, *AthleteID*)

A list is to be produced of the names of all athletes who are competing in the fixture that is taking place on 17/09/18. The list must include the Surname, Forename and DateOfBirth of these athletes and no other details. The list should be presented in alphabetical order by Surname.

Write an SQL query to produce the list.

I understand that you could do this two ways, one using a WHERE clause and the other using the INNER JOIN clause. However, I am wondering if the order matters when linking the tables.

First exemplar solution:

SELECT Surname, Forename, DateOfBirth
FROM Athlete, EventEntry, Fixture
WHERE FixtureDate = "17/09/2018"
 AND Athlete.AthleteID = EventEntry.AthleteID
 AND EventEntry.FixtureID = Fixture.FixtureID
ORDER BY Surname

Here is the first exemplar solution, would it still be correct if I was to switch the order of the tables in the WHERE clause, for example:

WHERE FixtureDate = "17/09/2018"
AND EventEntry.AthleteID = Athlete.AthleteID
AND Fixture.FixtureID = EventEntry.FixtureID

I have the same question for the INNER JOIN clause to, here is the second exemplar solution:

SELECT Surname, Forename, DateOfBirth
FROM Athlete 
INNER JOIN EventEntry ON Athlete.AthleteID = EventEntry.AthleteID 
INNER JOIN Fixture ON EventEntry.FixtureID = Fixture.FixtureID
WHERE FixtureDate = "17/09/2018"
ORDER BY Surname

Again, would it be correct if I used this order instead:

INNER JOIN EventEntry ON Fixture.FixtureID = EventEntry.FixtureID

If the order does matter, could somebody explain to me why it is in the order shown in the examples?

Answer 1

Some advice:

• Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.
• Use table aliases that are abbreviations for the table names.
• Use standard date formats!
• Qualify all column names.

Then, the order of the comparisons doesn't matter for equality. I would recommend using a canonical ordering.

So, the query should look more like:

SELECT a.Surname, a.Forename, a.DateOfBirth
FROM Athlete a INNER JOIN
     EventEntry ee
     ON a.AthleteID = ee.AthleteID INNER JOIN
     Fixture f
     ON ee.FixtureID = f.FixtureID
WHERE a.FixtureDate = '2018-09-17'
ORDER BY a.Surname;

I am guessing that all the columns in the SELECT come from Athlete. If that is not true, then adjust the table aliases.

Answer 2

There are lots of stylistic conventions for SQL and @gordonlinoff's answer mentions some of the perennial ones.

There are a few answers to your question.

The most important is that (notionally) SQL is a declarative language - you tell it what you want it to do, not how to do it. In a procedural language (like C, or Java, or PHP), the order of execution really matters - the sequence of instructions is part of the procedure. In a declarative language, the order doesn't really matter.

This wasn't always totally true - older query optimizers seemed to like the more selective where clauses earlier on in the statement for performance reasons. I haven't seen that for a couple of decades now, so assume that's not really a thing.

Because order doesn't matter, but correctly understanding the intent of a query does, many SQL developers emphasize readability. That's why we like explicit join syntax, and meaningful aliases. And for readability, the sequence of instructions can help. I favour starting with the "most important" table, usually the one from which you're selecting most columns, and then follow a logical chain of joins from one table to the next. This makes it easier to follow the logic.

Answer 3

When you use inner joins order does not matter as long as the prerequisite table is above/before. At your example both joins start from table Athlete so order doesn't matter. If however this very query is found starting from EventEntry (for any reason), then you must join at Athlete at the first inner else you cannot join to Fixture. As recommended, it is best to use standard join syntax and preferable place all inner joins before all lefts. If you cant then you need to review because the left you need to put inside the group of inner joins will probably behave like an inner join. That is because an inner below uses the left table else you could place it below the inner block. So when it comes to null the left will be ok but the inner below will cut the record.

When however the above cases do not exist/affect order and all inner joins can be placed at any order, only performance matters. Usually table with high cardinality on top perform better while there are cases where the opposite works better. So if the order is free you may try higher to lower cardinality tables ordering or the opposite - whatever works faster.

Clarifying: As prerequisite table i call the table needed by the joined table by condition: ... join B on [whatever] join C on c.id=b.cid - here table B is prerequisite for table C.

I mention left joins because while the question is about inner order, when joins are mixed (inners and lefts)then order of joins alone is important (to be all above) as may affect query logic: ... join B on [whatever] left join C on c.id=b.cid join D on D.id = C.did At the above example the left join sneaks into the inner joins order. We cannot order it after D because it is prerequisite for D. For records however where condition c.id=b.cid is not true the entire B table row turns null and then the entire result row (B+C+D) turns off the results because of D.id = C.did condition of the following inner join. This example needs review as the purpose of left join evaporates by the following (next on order) inner join. Concluding, the order of inner joins when mixed with lefts is better to be on top without any left joins interfering.