Join Effects With UPDATE

January 12, 2016

A lot of people don’t like UPDATE with a FROM clause. I’m going to explore what’s going on, both logically and within the query plan. And I’m doing this for T-SQL Tuesday #74, hosted by Robert Davis (@sqlsoldier).

I’m going to use a fresh copy of AdventureWorks2012. I’m using SQL 2012 SP3, but the things I’m looking at should apply to most versions. I’m going to use Production.Product and Production.ProductSubcategory. The Product table has a ProductSubcategoryID column with a foreign key in place, although this column allows nulls, as not every product must be within a subcategory.

Our standard UPDATE query doesn’t have a FROM clause. It’s just “UPDATE … SET … WHERE …”, and if we need to hook into other tables, we use sub-queries. Let’s look at why this is significant.

The WHERE clause filters rows. A sub-query in the WHERE clause still only filters rows, either by being a scalar expression used in one of the predicates, or being a single-column table expression used in an IN predicate, or a table expression used in an EXISTS clause. Any other tables used in sub-queries in the WHERE clause can only be used to help filter the table being updated – they can’t affect the SET clause at all, or cause a row to be updated multiple times.

Some examples are like this:

UPDATE Production.Product        
SET DiscontinuedDate = SYSDATETIME()         
WHERE ProductSubcategoryID = (SELECT s.ProductSubcategoryID FROM Production.ProductSubcategory s WHERE s.Name = 'Tights');    
UPDATE Production.Product        
SET DiscontinuedDate = SYSDATETIME()         
WHERE ProductSubcategoryID IN (SELECT s.ProductSubcategoryID FROM Production.ProductSubcategory s WHERE s.ProductCategoryID = 4);    
UPDATE Production.Product        
SET DiscontinuedDate = SYSDATETIME()         
WHERE EXISTS (SELECT * FROM Production.ProductSubcategory s  
WHERE s.ProductCategoryID = 4  
AND s.ProductSubcategoryID = Production.Product.ProductSubcategoryID);

UPDATE Production.Product

SET DiscontinuedDate = SYSDATETIME()

WHERE ProductSubcategoryID = (SELECT s.ProductSubcategoryID FROM Production.ProductSubcategory s WHERE s.Name = 'Tights');

UPDATE Production.Product

SET DiscontinuedDate = SYSDATETIME()

WHERE ProductSubcategoryID IN (SELECT s.ProductSubcategoryID FROM Production.ProductSubcategory s WHERE s.ProductCategoryID = 4);

UPDATE Production.Product

SET DiscontinuedDate = SYSDATETIME()

WHERE EXISTS (SELECT * FROM Production.ProductSubcategory s

WHERE s.ProductCategoryID = 4

AND s.ProductSubcategoryID = Production.Product.ProductSubcategoryID);

Using other tables in the SET clause generally means something that returns a scalar value, although this could become more complex using CASE. Still though, the logical impact on the overall query is notable. Something in the SET clause cannot be used to filter the values being updated, or to update a value multiple times. The SET clause is a list of “column = <scalar expression>” clauses, in which each column must come from the table (or table expression) being updated (which means I don’t think it should ever have a table alias), and cannot be listed multiple times. As the expression is scalar, it can’t produce multiple rows or columns.

UPDATE Production.Product        
SET DiscontinuedDate = (SELECT MAX(s.ModifiedDate)         
FROM Production.ProductSubcategory s  
WHERE s.ProductSubcategoryID = Production.Product.ProductSubcategoryID)         
WHERE ProductSubcategoryID IN (SELECT s.ProductSubcategoryID FROM Production.ProductSubcategory s WHERE s.ProductCategoryID = 4);

UPDATE Production.Product

SET DiscontinuedDate = (SELECT MAX(s.ModifiedDate)

FROM Production.ProductSubcategory s

WHERE s.ProductSubcategoryID = Production.Product.ProductSubcategoryID)

WHERE ProductSubcategoryID IN (SELECT s.ProductSubcategoryID FROM Production.ProductSubcategory s WHERE s.ProductCategoryID = 4);

Now, all UPDATE statements could be written like this. As an update statement cannot change the number of rows in a table, the net effect on any table is a single row of change (I know triggers could be used to have a larger effect, but that’s a separate topic). I’ve met plenty of people over the years who will argue for never using a FROM clause in an UPDATE clause.

You see, a FROM clause can have more of an effect than these sub-queries.

Let’s think about what introducing extra tables via a FROM clause can do. For now, let’s start with what’s going on in a SELECT query, when you turn a FROM clause into a FROM … JOIN clause.

1. It can let you access data in the columns of those tables, to use in predicates or expressions.    
2. It can let you filter the data in the base table, by only allowing rows which match, such as when using an inner join or right outer join.    
3. It can cause rows in the base table to be returned multiple times, if multiple rows in the joined table match a single row in the base table.    
4. It can introduce NULL rows, if a full or right outer join is being done (or a left outer join with the base table second) and there are rows in the joined table that don’t match any rows in the base table.

1. It can let you access data in the columns of those tables, to use in predicates or expressions.

2. It can let you filter the data in the base table, by only allowing rows which match, such as when using an inner join or right outer join.

3. It can cause rows in the base table to be returned multiple times, if multiple rows in the joined table match a single row in the base table.

4. It can introduce NULL rows, if a full or right outer join is being done (or a left outer join with the base table second) and there are rows in the joined table that don’t match any rows in the base table.

The particular combination of these will affect the type of join performed by your SELECT query – such as a Semi Join which does number 2, but none of the others. And if it does none, then the join is redundant and won’t appear in the query plan at all.

So how does this work in an UPDATE statement?

There are two possible ways that a FROM clause can work – one is to include the table being updated in the FROM clause, and the other is to have it NOT included in the FROM clause. If it doesn’t appear in the FROM clause, then predicates to define the matching criteria must be included in the WHERE clause to avoid updating every row. If it does appear in the FROM clause, then I would recommend using the table alias in the UPDATE clause rather than the name of the table itself.

(Interestingly PDW does not support “UPDATE … FROM … JOIN”, although “UPDATE … FROM … WHERE” is fine.)

So this is fine:

UPDATE Production.Product       
SET DiscontinuedDate = s.ModifiedDate        
FROM Production.ProductSubcategory s        
WHERE s.ProductSubcategoryID = Production.Product.ProductSubcategoryID;

UPDATE Production.Product

SET DiscontinuedDate = s.ModifiedDate

FROM Production.ProductSubcategory s

WHERE s.ProductSubcategoryID = Production.Product.ProductSubcategoryID;

As is this:

UPDATE p       
SET DiscontinuedDate = s.ModifiedDate        
FROM Production.Product p        
JOIN Production.ProductSubcategory s        
ON s.ProductSubcategoryID = p.ProductSubcategoryID;

UPDATE p

SET DiscontinuedDate = s.ModifiedDate

FROM Production.Product p

JOIN Production.ProductSubcategory s

ON s.ProductSubcategoryID = p.ProductSubcategoryID;

But please be careful about:

UPDATE Production.Product       
SET DiscontinuedDate = s.ModifiedDate        
FROM Production.Product p        
JOIN Production.ProductSubcategory s        
ON s.ProductSubcategoryID = p.ProductSubcategoryID;

UPDATE Production.Product

SET DiscontinuedDate = s.ModifiedDate

FROM Production.Product p

JOIN Production.ProductSubcategory s

ON s.ProductSubcategoryID = p.ProductSubcategoryID;

It works, but I don’t consider it safe. Because you have the potential to update a table which isn’t mentioned in the FROM clause, you could find yourself inadvertently updating every row in Production.Product. There are safeguards to prevent it happening – this next example gives an error:

UPDATE Production.Product       
SET DiscontinuedDate = SYSDATETIME()        
FROM Production.Product p        
WHERE Production.Product.ProductSubcategoryID IS NOT NULL;

UPDATE Production.Product

SET DiscontinuedDate = SYSDATETIME()

FROM Production.Product p

WHERE Production.Product.ProductSubcategoryID IS NOT NULL;

, although this one doesn’t, and updates every row in the table – after all, we have a CROSS JOIN going on, because I’ve listed the wrong table.

UPDATE Production.Product       
SET DiscontinuedDate = SYSDATETIME()        
FROM Production.ProductSubcategory p        
WHERE p.ProductSubcategoryID IS NOT NULL;

UPDATE Production.Product

SET DiscontinuedDate = SYSDATETIME()

FROM Production.ProductSubcategory p

WHERE p.ProductSubcategoryID IS NOT NULL;

If I’m writing queries, it’s generally fine. But if there’s a system which produces dynamic SQL, I start to worry. I’d rather update the alias, and be completely clear about what’s going on.

So let’s go with the idea of using the table alias in the UPDATE clause when using the FROM clause, and choosing to always include the table being updated in the FROM clause. Unless we’re using PDW, of course.

But the impact of those join effects… let’s look at them.

Earlier, we saw this query. An inner join between Product and ProductSubcategory.

UPDATE p       
SET DiscontinuedDate = s.ModifiedDate        
FROM Production.Product p        
JOIN Production.ProductSubcategory s        
ON s.ProductSubcategoryID = p.ProductSubcategoryID;

UPDATE p

SET DiscontinuedDate = s.ModifiedDate

FROM Production.Product p

JOIN Production.ProductSubcategory s

ON s.ProductSubcategoryID = p.ProductSubcategoryID;

Now, because s.ProductSubcategoryID is known to be unique (it’s the primary key on s), there is no way that this can cause ‘multiple updates’ to Product. Things are okay here, but filtering could certainly apply. A join is done to get the values from ProdcutSubcategory, and the rows are fed into the Clustered Index Update operator.

Filters are okay here. UPDATE is happy with filters, whether they’re implemented using the WHERE clause or via an ON clause.

But what if the unique index weren’t there? Then we might see duplicate rows – the next join effect.

ALTER TABLE [Production].[Product] DROP CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID];       
ALTER TABLE [Production].[ProductSubcategory] DROP CONSTRAINT [PK_ProductSubcategory_ProductSubcategoryID];

1 2	ALTER TABLE [Production].[Product] DROP CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID]; ALTER TABLE [Production].[ProductSubcategory] DROP CONSTRAINT [PK_ProductSubcategory_ProductSubcategoryID];

Now what does the plan look like – we should see a Table Scan instead of a Clustered Index Scan because we just dropped the PK, but what other differences?

It looks very similar, but now throws a Distinct Sort in there. You see, an Update isn’t going to do multiple updates. It won’t allow it. So it does a Distinct Sort on the PK of the Product table, and uses whichever value it cares to for the update.

Another option it could’ve used would’ve been to use an Aggregate operator (because GROUP BY and DISTINCT are essentially the same thing), in which case it would’ve needed to apply an aggregate function to s.ModifiedDate while grouping by the Product PK. Which aggregate? The ANY() aggregate, of course – because it doesn’t care which value to use, it just has to be a valid one. I can get this plan by using an OPTION (FAST 1) query hint, because that will avoid doing the Sort, as a Sort is blocking. It also turns the Hash Match into a Nested Loop, because it really wants to get that first row through as quickly as possible. It’s a slower query, but lets us see the ANY() aggregate.

So we can see that if a multiple rows are going to be returned by the FROM clause, this will get shrunk down to a single one. This is how that third ‘join effect’ is handled.

Be really careful about this. It’s a bad thing, and the reason why purists don’t like to see a FROM clause in an UPDATE statement.

The next (and final – yay!) join effect is to have NULLs introduced.

Let’s start by putting those constraints back in:

ALTER TABLE [Production].[ProductSubcategory] ADD  CONSTRAINT [PK_ProductSubcategory_ProductSubcategoryID] PRIMARY KEY CLUSTERED        
([ProductSubcategoryID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];        
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID] FOREIGN KEY([ProductSubcategoryID])        
REFERENCES [Production].[ProductSubcategory] ([ProductSubcategoryID]);        
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID];

ALTER TABLE [Production].[ProductSubcategory] ADD CONSTRAINT [PK_ProductSubcategory_ProductSubcategoryID] PRIMARY KEY CLUSTERED

([ProductSubcategoryID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

ALTER TABLE [Production].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID] FOREIGN KEY([ProductSubcategoryID])

REFERENCES [Production].[ProductSubcategory] ([ProductSubcategoryID]);

ALTER TABLE [Production].[Product] CHECK CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID];

…and let’s put a RIGHT JOIN in there (for purely academic reasons – I know you’d never do this in real life, although you might put a LEFT JOIN in with the base table second). This means that our FROM clause will return an extra row for each ProductSubcategory that has no Products. There aren’t any of them in AdventureWorks2012, but the Query Optimizer doesn’t know that.

Before I go any further, let’s quickly make something clear. With a right outer join, the result set of the join contains rows that don’t exist in the base table. Obviously we can’t update those – there’s nothing in the base table for those rows. But we’re going to look at how the query plan handles this situation.

UPDATE p       
SET DiscontinuedDate = s.ModifiedDate        
FROM Production.Product p        
RIGHT JOIN Production.ProductSubcategory s        
ON s.ProductSubcategoryID = p.ProductSubcategoryID;

UPDATE p

SET DiscontinuedDate = s.ModifiedDate

FROM Production.Product p

RIGHT JOIN Production.ProductSubcategory s

ON s.ProductSubcategoryID = p.ProductSubcategoryID;

There are two things of note here in the query plan (apart from the fact that it has put the base table second and used a left join):

For a start, we still see a Distinct Sort! I can assure you that the unique constraint is in there. If I remove the keyword ‘RIGHT’ I go back to my original version without any distinctifying operator. But the thing that’s new here is that Filter. Let’s look at what the Filter is doing:

It’s filtering on “[IsBaseRow1001] IS NOT NULL”. That value is coming from the Product table, and is simply checking to see whether the row coming out of the Join operator is a match or not. It’s testing to see if we actually have a row in the base table to update. It could’ve tested ProductID for NULL for this, like we would in a query, but I guess it’s quicker to test IsBaseRow for being NULL than to test ProductID. I don’t know much about IsBaseRow1001, but I can tell that it’s not more than a single byte. The Estimated Row Size on the Scan of Product was 15B in the original query, and is 16B in this query. But I’m just guessing here. Theoretically it’s not needed at all, of course, and for testing, could have been a single bit.

Or the Query Optimizer could have turned the join into an inner join. After all, we’re not interested in updating a non-row. As much as it’s interesting to see IsBaseRow1001 coming through, I can’t help but think that turning that join operator into an inner join would’ve done the trick. But as we don’t see LEFT JOIN + NULL being turned into an Anti Semi Join either, I’m not too surprised that this translation isn’t happening either.

Because there could be multiple Subcategories without Products, there is a possibility of non-unique ProductIDs – the NULLs – coming out of the Join operator. But these are the only ones that could be duplicates, because each Product has at most a single matching ProductSubcategoryID in s. Therefore, once the NULLs have been removed by the Filter, the QO should be able to know that the data coming out of the Filter is unique on ProductID, but it doesn’t use this information, and needs a distinctifying operator to be sure.

The Distinct Sort is still on ProductID, but a FAST 1 hint turns it into a Hash Aggregate this time instead of a Stream Aggregate. The reason for this is that a Nested Loop over the Product table isn’t going to find the ProductSubcategories that don’t have Products (although it could if it understood the translation to Inner Join). Therefore, it still performs a Hash Aggregate, does the filter for IsBaseRow1001, and then does a Hash Match (Flow Distinct) on ProductID.

It’s interesting to see that we have a Build Residual here on ProductID, despite ProductID being an integer.

You see, normally in a Hash Match on an integer we wouldn’t see a residual because the hash function produces an integer. It’s because ProductID could have been NULL. The nullability of the column coming through obviously wasn’t change by the Filter (and let’s face it – it didn’t test ProductID for NULL, it tested IsBaseRow1001).

Quick interlude for showing that the hash function produces a 32-bit value, and doesn’t need a residual check when hashing on a non-nullable integer (while a nullable integer needs more than 32 bits):

Compare the plans of these two queries. The Hash Match operator in the first one doesn’t have a Probe Residual, because s.ProductCategoryID doesn’t allow NULLs. The Hash Match operator in the second does have a Probe Residual, because p.ProductSubcategoryID does allow NULLs, and a nullable integer can cause clashes in the hash table.

SELECT *         
FROM Production.ProductSubcategory s          
JOIN Production.ProductCategory c ON c.ProductCategoryID = s.ProductCategoryID          
OPTION (HASH JOIN);    
SELECT *         
FROM Production.Product p          
JOIN Production.ProductSubcategory s ON s.ProductSubcategoryID = p.ProductSubcategoryID          
OPTION (HASH JOIN);

SELECT *

FROM Production.ProductSubcategory s

JOIN Production.ProductCategory c ON c.ProductCategoryID = s.ProductCategoryID

OPTION (HASH JOIN);

SELECT *

FROM Production.Product p

JOIN Production.ProductSubcategory s ON s.ProductSubcategoryID = p.ProductSubcategoryID

OPTION (HASH JOIN);

Also consider the Hash Match operator in the following query:

CREATE TABLE dbo.rf_BigInts (id BIGINT NOT NULL PRIMARY KEY);    
SELECT *         
FROM dbo.rf_BigInts t1          
JOIN dbo.rf_BigInts t2 ON t2.id = t1.id          
OPTION (HASH JOIN);    
DROP TABLE dbo.rf_BigInts;

CREATE TABLE dbo.rf_BigInts (id BIGINT NOT NULL PRIMARY KEY);

SELECT *

FROM dbo.rf_BigInts t1

JOIN dbo.rf_BigInts t2 ON t2.id = t1.id

OPTION (HASH JOIN);

DROP TABLE dbo.rf_BigInts;

, where we see a Probe Residual on a non-nullable bigint (a lot more than 32 bits). This tells me that bigints can have clashes in the hash table, despite non-nullable integers not showing this.

Oh yes, we were looking at the Build Residual.

If we went back to an Inner Join with FAST 1, where we got a Stream Aggregate, and turn that into a Hash Match on the non-nullable ProductID, we can see that our Build Residual disappears.

ALTER TABLE [Production].[Product] DROP CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID];       
ALTER TABLE [Production].[ProductSubcategory] DROP CONSTRAINT [PK_ProductSubcategory_ProductSubcategoryID];    
UPDATE p       
SET DiscontinuedDate = s.ModifiedDate        
FROM Production.Product p        
JOIN Production.ProductSubcategory s        
ON s.ProductSubcategoryID = p.ProductSubcategoryID        
OPTION (HASH JOIN, FAST 1);

ALTER TABLE [Production].[Product] DROP CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID];

ALTER TABLE [Production].[ProductSubcategory] DROP CONSTRAINT [PK_ProductSubcategory_ProductSubcategoryID];

UPDATE p

SET DiscontinuedDate = s.ModifiedDate

FROM Production.Product p

JOIN Production.ProductSubcategory s

ON s.ProductSubcategoryID = p.ProductSubcategoryID

OPTION (HASH JOIN, FAST 1);

Let’s do a bit of a summary…

If you’re doing an UPDATE, you can only update each row in that table one time, no matter what kind of impact your FROM clause might want to have on the base table. You may feel like it’s going to update some rows multiple times, but that’s not how it works

Each of the join effects is either applied (in the case of a filter) or mitigated (in the case of duplicates or NULLs), so that you can access the data in other tables without fear of having a bad effect on your UPDATE, but don’t do it! Because you can’t tell which value gets picked up by the ANY() aggregate (or first row by a distinctifying operator), you should avoid duplicates completely, so that your UPDATE finds a single value to update each row with.

And I would encourage you to use table aliases in your UPDATE clause if you use a FROM clause with a JOIN – but if you don’t use a JOIN, then make sure you include the match in your WHERE clause.

@rob_farley

This Post Has 9 Comments

RichB January 14, 2016 Reply

Hi…
Curious about:
"If you’re doing an UPDATE, you can only update a single row in that table, no matter what kind of impact your FROM clause might want to have on the base table."
Not entirely sure I’ve followed what you are saying.
Rob Farley January 14, 2016 Reply

Ah – typo. Missed the word "once". You can only update each row one time in an update, even if the query makes it feel like you’re doing it multiple times.
Gan Davnarrain January 17, 2016 Reply

Very useful and instructive article. Thanks.
Robert L Davis January 20, 2016 Reply

Thanks for participating. Great article. I never actually realized that the table in the UPDATE clause could differ from the table in the criteria of the query. Never considered it. I have always used UPDATE x From dbo.Table x JOIN ….
Rob Farley January 20, 2016 Reply

Yeah, I think most people have patterns they use, and don’t necessarily think about what might happen if they change the pattern. I’m glad you enjoyed it, Robert (and you, Gan!).
Dixie S. January 21, 2016 Reply

Regarding using the full table name in the update clause instead of an alias: "Because you have the potential to update a table which isn’t mentioned in the FROM clause, you could find yourself inadvertently updating every row in Production.Product. "
Will you please explain this a little more? — I’m not sure I understand it.
Is the danger only if you make a mistake and don’t type the exact same table name in the From clause as in the Update clause? I prefer this syntax because to me, it’s clearer to have the table name, rather than an alias, in the Update clause.
But then, to save some typing, I alias the tables in the From clause.
You can imagine how surprised to find my "style" in the unsafe category!
These characteristics of the Update statement are news to me and I appreciate your taking the time to share your insights!
Rob Farley January 21, 2016 Reply

Hi Dixie,
What do you do if you need to reference the table twice in your FROM clause? Would that persuade you to update the alias?
By updating the alias, you remove any doubt about which table in the FROM clause is the base table, although you can do it in your style if there is no ambiguity. Hopefully you’ll never update a table which isn’t mentioned in either the FROM or WHERE clause, but if you always update the alias, you’ll be safe from that potential mistake.
If you never make a mistake, any pattern is safe enough, right?
Dixie S. January 22, 2016 Reply

You have a point there! I’m not saying I won’t make a mistake and I’ll be even more careful after reading your post. I tend to use Update with a join only for the simplest of queries, joining on PK, FK. Otherwise, I use subqueries or Merge (with a CTE) to prevent the danger of two rows being found in the source to update one row of the target.
Yours is the best explanation I’ve read about the various ways of writing and Update statement!
Thank you for your reply.
Krishnamurthy K August 4, 2016 Reply

Hi Rob,
I have query in PDW.when I try update null data(where the column doesn’t accept null) it is throwing me "object reference not set" error for few packages and for few packages it is throwing me "column has null value" error.why this is so.
Thank you