T-SQL: Selecting rows to delete via joins

John_30006 0
Viewed 428469

Scenario:

Let's say I have two tables, TableA and TableB. TableB's primary key is a single column (BId), and is a foreign key column in TableA.

In my situation, I want to remove all rows in TableA that are linked with specific rows in TableB: Can I do that through joins? Delete all rows that are pulled in from the joins?

DELETE FROM TableA 
FROM
   TableA a
   INNER JOIN TableB b
      ON b.BId = a.BId
      AND [my filter condition]

Or am I forced to do this:

DELETE FROM TableA
WHERE
   BId IN (SELECT BId FROM TableB WHERE [my filter condition])

The reason I ask is it seems to me that the first option would be much more effecient when dealing with larger tables.

Thanks!

join tsql
12 Answers
DELETE TableA
FROM   TableA a
       INNER JOIN TableB b
               ON b.Bid = a.Bid
                  AND [my filter condition]

should work

TheTXI_221640

I would use this syntax

Delete a 
from TableA a
Inner Join TableB b
on  a.BId = b.BId
WHERE [filter condition]
cmsjr_231140

Yes you can. Example :

DELETE TableA 
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]
Diadistis_474010 
DELETE FROM table1
where id IN 
    (SELECT id FROM table2..INNER JOIN..INNER JOIN WHERE etc)

Minimize use of DML queries with Joins. You should be able to do most of all DML queries with subqueries like above.

In general, joins should only be used when you need to SELECT or GROUP by columns in 2 or more tables. If you're only touching multiple tables to define a population, use subqueries. For DELETE queries, use correlated subquery.

user_14355150
Related