DELETE ... FROM ... JOIN (explanation needed)

Question

DELETE ... FROM ... JOIN (explanation needed)

mrlucmorin

Old Hand

Points: 306
More actions
May 12, 2009 at 6:22 am

#133865

Hi,
When you issue a statement such as:
DELETE TableA
FROM TableA INNER JOIN TableB
ON TableA.a = TableB.b
how does the server "know" which row to delete ? I mean, there's obviously no PK involved, so how does the server uniquely identify each row of the JOIN to then apply the DELETE ?
(Hope this makes sense)
Thank you
Luc Morin, T.P.
http://www.stlm.ca

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

All the rows in TableA that satisfy the join condition (ie have matching rows in TableB) will be deleted.

Basically, iy you replace DELETE FROM TableA with SELECT TableA.*, you'll see all the rows that match. Those are the ones that will be deleted.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 2

starting with your query (I corrected the syntax):

DELETE FROM TableA

FROM TableA INNER JOIN TableB

ON TableA.a = TableB.b

turn it into a select

Select TableA.*

FROM TableA INNER JOIN TableB

ON TableA.a = TableB.b

every row which appears in that select is a row that will be deleted from tableA

As far as I know, this isn't ANSI standard SQL

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Samuel Vella (5/12/2009)
As far as I know, this isn't ANSI standard SQL

You are correct, it's not.

There was nothing wrong with his syntax. That first FROM is optional. These are equivalent and both work

delete Table1

delete from Table1

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mrlucmorin Old Hand Points: 306 More actions · Answer 4

Hi,

Thanks both for the quick reply.

I guess that internally, the server "knows" which rows of TableA are included in the JOIN, though what I'd like to know is how it knows 🙂

But as an end user, the internals are not all that important. What matters is that I can safely rely on the fact that if the row is included in the JOIN, then it will be deleted, and vice versa. Turning the query into a SELECT query is a good trick.

Thanks again

Luc Morin, T.P.
http://www.stlm.ca

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

mrlucmorin (5/12/2009)
I guess that internally, the server "knows" which rows of TableA are included in the JOIN, though what I'd like to know is how it knows 🙂

It will start, just as with a select, retrieving the rows from TableA and TableB. It will do the join (probably as a semi-join as the data from tableB is not needed), then it will use those rows as a 'filter' for the delete instead of returning them as it would for a select.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mrlucmorin Old Hand Points: 306 More actions · Answer 6

Hi,

This is a very powerful technique.

I'm just stating to think in terms of result set instead of thinking in terms of sequential programming (cursors) for my triggers.

This technique will prove useful I think (Now that I understand it better)

Thank you.

Luc Morin, T.P.
http://www.stlm.ca

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

mrlucmorin (5/12/2009)
I'm just stating to think in terms of result set instead of thinking in terms of sequential programming (cursors) for my triggers.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

mrlucmorin (5/12/2009)
Hi,
Thanks both for the quick reply.
I guess that internally, the server "knows" which rows of TableA are included in the JOIN, though what I'd like to know is how it knows 🙂

No internal magic is necessary, it knows because your FROM clause told it how to figure it out:

FROM TableA

INNER JOIN TableB

ON TableA.a = TableB.b

So it compares every row in TableA to every row in TableB to find which ones match in column [a] of TableA to column of TableB. Anytime they match, it "appends" (or concatenates) the TableB row onto the TableA row and then returns it to the resultset.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]