How does t-sql update work without a join

Question

I think my head is muddy or something. I'm trying to figure out how a t-sql update works without a join when updating one table from another. I've always used joins in the past but came across a stored proc where someone else created one without a join. This update is being used in SQL 2008R2 and it works.

Update table1

SET col1 = (SELECT TOP 1 colX FROM table2 WHERE colZ = colY),

col2 = (SELECT TOP 1 colE FROM table2 WHERE colZ = colY)

Obviously, colY is a field in table1. To get the same results in a select statement (not update), a join is required. I guess I don't understand how an update works behind the scenes but it must be doing some kind of join?

Answer 1

SQL Server translates those subqueries into joins. You can look at this by getting the query plan. You can write an equivalent query with UPDATE ... FROM ... JOIN syntax and observe the query plan to be essentially the same.

The sample code shown is unusual, hard to understand, redundant and inflexible. I recommend against using this style.