Does the Order of Tables in the Join clauses of the Update Statement Matter
-
Suppose I have 3 tables as seen below:
I would like to update the Name column of the Manufacturer Table based on the ModelID column of the Model table
Below is my update statement
Update Manufacturer
Set m.Name = "Company3"
from Manufacturer m
Inner Join Project p
On m.ProjectID = p.ID
Inner Join Model md
On md.CollabID = p.CollabID
Where m.Model = FGT and CollabID = "XDZ3G"What I would like to know is does the order of the table used in the Join inside of the From clause matter.
For instance if the following is the From clause
From Project p Inner Join Manufacturer m
On p.ID = m.ProjectID
Inner Join Model md
On md.CollabID = p.CollabID
Where m.Model = FGT and CollabID = "XDZ3G"In the From clause above the Manufacturer table is listed first followed by the Project table in the join.
Does this affect the result of this update and if so how? -
Th order of the tables only matters on the joins. In other words, you cannot join to an object that has not yet been used higher up in the list of joined objects.
Also note, that the optimizer may rewrite/re-organize your joins.
-
So you are saying that the update will only happen after all the joins are complete and not before?
- SQLUSERMAN - Sunday, March 10, 2019 9:00 AM
Correct. All the joins have to be evaluated in order to determine the set of records that need to be updated.
- SQLUSERMAN - Sunday, March 10, 2019 4:35 AMSuppose I have 3 tables as seen below:
I would like to update the Name column of the Manufacturer Table based on the ModelID column of the Model table
Below is my update statement
Update Manufacturer
Set m.Name = "Company3"
from Manufacturer m
Inner Join Project p
On m.ProjectID = p.ID
Inner Join Model md
On md.CollabID = p.CollabID
Where m.Model = FGT and CollabID = "XDZ3G"What I would like to know is does the order of the table used in the Join inside of the From clause matter.
For instance if the following is the From clause
From Project p Inner Join Manufacturer m
On p.ID = m.ProjectID
Inner Join Model md
On md.CollabID = p.CollabID
Where m.Model = FGT and CollabID = "XDZ3G"In the From clause above the Manufacturer table is listed first followed by the Project table in the join.
Does this affect the result of this update and if so how?Additionally, you have a syntax error: Your reference to CollabID in the WHERE clause would cause an error because there is more than one such column in all the tables joined. You would need to specify the table alias for that column.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy) -
The optimizer will workout the best order to join the tables. So it will make no difference. However, there is a hint (FORCE ORDER) you can use to force the order of the joins to be the same as the order in your query.
- Jonathan AC Roberts - Monday, March 11, 2019 7:52 AM
Actually, the optimizer won't necessarily work out the "best order". It will frequently give up with a "good enough solution" that's not actually good enough if it takes too long.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions - Jeff Moden - Monday, March 11, 2019 9:01 AM
Which I suppose is why they have a hint for it.
- Jonathan AC Roberts - Monday, March 11, 2019 9:37 AM
Z'actly. 😉
And, while it bucks the traditional thoughts of it not mattering what the order in the from clause is, I've found that the order can help SQL Server find a better plan.
Of course, that's a 100% anecdotal claim that's not worth a hoot in a holler and I don't have a coded example to clearly demonstrate that little nuance. This has come up enough times where I'm going to keep my eyes peeled for where it does make a difference and see if I can come up with something demonstrable to share.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply