T-SQL: Selecting rows to delete via joins

513

votes

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!

tsqljoin

758

votes

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

should work

266

votes

I would use this syntax

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

30

votes

Yes you can. Example :

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

11

votes

Was trying to do this with an access database and found I needed to use a.* right after the delete.

DELETE a.*
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]

9

votes

It's almost the same in MySQL, but you have to use the table alias right after the word "DELETE":

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

3

votes

The syntax above doesn't work in Interbase 2007. Instead, I had to use something like:

DELETE FROM TableA a WHERE [filter condition on TableA] 
  AND (a.BId IN (SELECT a.BId FROM TableB b JOIN TableA a 
                 ON a.BId = b.BId 
                 WHERE [filter condition on TableB]))

(Note Interbase doesn't support the AS keyword for aliases)

3

votes

I'm using this

DELETE TableA 
FROM TableA a
INNER JOIN
TableB b on b.Bid = a.Bid
AND [condition]

and @TheTXI way is good as enough but I read answers and comments and I found one things must be answered is using condition in WHERE clause or as join condition. So I decided to test it and write an snippet but didn't find a meaningful difference between them. You can see sql script here and important point is that I preferred to write it as commnet because of this is not exact answer but it is large and can't be put in comments, please pardon me.

Declare @TableA  Table
(
  aId INT,
  aName VARCHAR(50),
  bId INT
)
Declare @TableB  Table
(
  bId INT,
  bName VARCHAR(50)  
)

Declare @TableC  Table
(
  cId INT,
  cName VARCHAR(50),
  dId INT
)
Declare @TableD  Table
(
  dId INT,
  dName VARCHAR(50)  
)

DECLARE @StartTime DATETIME;
SELECT @startTime = GETDATE();

DECLARE @i INT;

SET @i = 1;

WHILE @i < 1000000
BEGIN
  INSERT INTO @TableB VALUES(@i, 'nameB:' + CONVERT(VARCHAR, @i))
  INSERT INTO @TableA VALUES(@i+5, 'nameA:' + CONVERT(VARCHAR, @i+5), @i)

  SET @i = @i + 1;
END

SELECT @startTime = GETDATE()

DELETE a
--SELECT *
FROM @TableA a
Inner Join @TableB b
ON  a.BId = b.BId
WHERE a.aName LIKE '%5'

SELECT Duration = DATEDIFF(ms,@StartTime,GETDATE())

SET @i = 1;
WHILE @i < 1000000
BEGIN
  INSERT INTO @TableD VALUES(@i, 'nameB:' + CONVERT(VARCHAR, @i))
  INSERT INTO @TableC VALUES(@i+5, 'nameA:' + CONVERT(VARCHAR, @i+5), @i)

  SET @i = @i + 1;
END

SELECT @startTime = GETDATE()

DELETE c
--SELECT *
FROM @TableC c
Inner Join @TableD d
ON  c.DId = d.DId
AND c.cName LIKE '%5'

SELECT Duration    = DATEDIFF(ms,@StartTime,GETDATE())

If you could get good reason from this script or write another useful, please share. Thanks and hope this help.

2

votes

Let's say you have 2 tables, one with a Master set (eg. Employees) and one with a child set (eg. Dependents) and you're wanting to get rid of all the rows of data in the Dependents table that cannot key up with any rows in the Master table.

delete from Dependents where EmpID in (
select d.EmpID from Employees e 
    right join Dependents d on e.EmpID = d.EmpID
    where e.EmpID is null)

The point to notice here is that you're just collecting an 'array' of EmpIDs from the join first, the using that set of EmpIDs to do a Deletion operation on the Dependents table.

2

votes

In SQLite, the only thing that work is something similar to beauXjames' answer.

It seems to come down to this DELETE FROM table1 WHERE table1.col1 IN (SOME TEMPORARY TABLE); and that some temporary table can be crated by SELECT and JOIN your two table which you can filter this temporary table based on the condition that you want to delete the records in Table1.

2

votes

You can run this query:

    DELETE FROM TableA
    FROM
       TableA a, TableB b 
    WHERE
       a.Bid=b.Bid
    AND
       [my filter condition]

1

votes

The simpler way is:

DELETE TableA
FROM TableB
WHERE TableA.ID = TableB.ID

1

votes

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.

T-SQL: Selecting rows to delete via joins

12 Answers