SQL Question: Does the order of the WHERE/INNER JOIN clause when interlinking table matter?
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?
Does the join order matter in SQL?, The order doesn't matter for INNER joins. As long as you change your selects from SELECT * to SELECT a.*, b.*, c.* the queries will return the� I had a great question submitted to me (thank you Brandman!) that I thought would make for a good blog post: …I’ve been wondering if it really matters from a performance standpoint where I start my queries. For example, if I join from A-B-C, would I be better off starting at table B and then … Continue reading "Does The Join Order of My Tables Matter?"
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.
SQL INNER JOIN, SQL INNER JOIN returns all rows from tables where the key record of one table is equal to An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables. the following SQL statement can be used : London Pot Rice Pcs Jack Hill Ltd London Jaffa Cakes Pcs Order All Boston. The order of the JOINs (as long as we're talking about INNER JOINS) does not matter. Optimizer will re-arrange joins internally as it finds the best for the query. For the OUTER joins the order of joins is very important. For every expert, there is an equal and opposite expert.
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.
QUERYING THE DATABASE: QUERIES and VIEWS, The table names are only required in the FROM, JOIN, and ON clauses, and in the latter, INNER JOIN: Select only those rows that have values in common in the columns Order Details Extended: Calculates the Extended Price, the discounted total for each Can you sort on the calculated column in the SQL statement? Hi, I think Order of columns does matter and it is to do with the Index utilization. What I understand and have seen is if there is an index defined and the order of index definition is different from the order in which the columns are used in the WHERE condition, then there is a possibility that the index may not be used.
When Join Order Matters, It's a popular belief that the order of a SQL query's join order doesn't matter so long as the joins are done as an inner join . A common question among new SQL query users is "Does the order of my inner joins matter?". for a query block is proportional to the number of join items in the FROM clause. “No” it doesn’t matter, in the sense that if you have a inner join between two different tables, and you put your filter criteria in the join portion, Oracle should do the same thing either way. I say “should”…sometimes, things that “should” be true in a database, aren’t.
Joining Data in SQL, INNER JOIN requires a specification of the key field (or fields) in each table. AND clause can check that multiple conditions are met. with the alias of p1; The order matters and if you switched the two tables, the output would be slightly different. Use INTERSECT to answer this question with countries and cities ! SQL join tables with group by and order by Last update on February 26 2020 08:07:43 (UTC/GMT +8 hours) In this page, we are going to discuss the usage of GROUP BY and ORDER BY clause within a join.
It's a popular belief that the order of a SQL query's join order doesn't matter so long as the joins are done as an inner join . In a simple world, this is true. Unfortunately, life is rarely so simple. A common question among new SQL query users is "Does the order of my inner joins matter?". The answer to that question, like many things in life, tends to be more complicated than one might originally think.
- It doesn't matter for the equality operator
- As a consequence of 'it doesn't matter..' the answer for the last your question is yes, it is correct as it can only change the order of joins.
- Once your questions are clear, they are going to be faqs. (Obviously.) Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See How to Ask & the voting arrow mouseover texts. PS stackoverflow.com/a/25957600/3404097
- (Again:) It's clear that your "second exemplar solution" differs from the 1st by switching the last 2 WHERE conjuncts. But it's not clear what you are trying to say by "Again, would it be correct if I used this order instead:
INNER JOIN EventEntry ON Fixture.FixtureID = EventEntry.FixtureID". Do you mean
ON Fixture.FixtureID = EventEntry.FixtureIDinstead & are you talking about switching the
EventEntry.FixtureID = Fixture.FixtureID? Otherwise, what are you trying to say? What is the entire "3rd exemplar solution"? Please clarify via edits, not comments.
- Possible duplicate of SQL join: where clause vs. on clause
- Thanks for the advice, though this is actually the example they provided in the mark scheme. So I am assuming by using a
INNER JOINclause it would not matter if I was to write
INNER JOIN EventEntry ON Fixture.FixtureID = EventEntry.FixtureIDinstead of
INNER JOIN Fixture ON EventEntry.FixtureID = Fixutre.FixtureID.
- @HenryLi . . . The order of equality comparisons doesn't matter at all.
- @HenryLi Of course it would matter if you wrote that instead because the 2 JOINs use two different tables. What exactly are you trying to say? Please clarify via edits not comments. (Is that comment question supposed to be the same as the 2nd question in your post? See my last comment on your post re that. But you should ask one question per post & none in comments.)
- Your first sentence is correct, although not very clear. That is really all you need, if you explain "the prerequisite table". (Use enough words, phrases & sentences to say what you mean.) But the next 2 sentences are not clear, they seem to contradict the first sentence, they give no details & they do not explain why their antecedents imply their consequents. Then your language about left join is very unclear. There I know what you are trying to say but you are not saying it. (Also the question is about inner join.) You don't use enough words & you use vague shorthands like "starting from".
- Adding to something unclear does not make it clear. PS Please don't insert EDITs/UPDATEs, just make your post the best presentation as of right now.