Understanding SQL DELETE with JOIN: A Simple Guide

This confused me for a few minutes today and I wanted to share to help avoid further confusion.

The specific code was this:


DELETE t2
FROM [dbo].[Table1] t1
INNER JOIN [dbo].[Table2] t2 on t1.favColor = t2.id;

Names have been changed to protect the innocent.

In the above delete statement which table will have rows deleted from it?

A: Table1

B: Table2

C: Both Table1 and Table2

D: Neither Table1 and Table2

The answer is Table2. However at first glance I thought it was going to be Table1. Then I wrote the following sample code to prove the results.

Here is the full example script to show the results.


CREATE DATABASE weirdJoinDelete;
GO
USE weirdJoinDelete;
GO

DROP TABLE [dbo].[Table1];
DROP TABLE [dbo].[Table2];
GO
CREATE TABLE [dbo].[Table1](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NOT NULL,
[favColor] [int] NULL
);
GO

CREATE TABLE [dbo].[Table2](
[id] [int] NOT NULL,
[FavoriteColor] [varchar](100) NOT NULL
) ;

INSERT INTO [dbo].[Table2] values(1, 'Red');
INSERT INTO [dbo].[Table2] values(2, 'Yellow');
INSERT INTO [dbo].[Table2] values(3, 'Green');

INSERT INTO [dbo].[Table1]([Name], [FavColor]) values('Fred', 1);
INSERT INTO [dbo].[Table1]([Name], [FavColor]) values('Mary', 2);
INSERT INTO [dbo].[Table1]([Name], [FavColor]) values('Jon', 2);
INSERT INTO [dbo].[Table1]([Name], [FavColor]) values('Ann', 3);

SELECT *
FROM [dbo].[Table1];

SELECT *
FROM [dbo].[Table2];

DELETE t2
FROM [dbo].[Table1] t1
INNER JOIN [dbo].[Table2] t2 on t1.favColor = t2.id;

-- which table will have rows missing????

SELECT *
FROM [dbo].[Table1];

SELECT *
FROM [dbo].[Table2];

Before the delete the tables look like this:

After the delete, the tables look like this:

You can see that Table2 is the table that is missing the rows.

What if the SQL was written like this?


DELETE t2
FROM [dbo].[Table2] t2
INNER JOIN [dbo].[Table1] t1 on t1.favColor = t2.id;

Still Table2 would have rows deleted from it.

How about this?


DELETE t1
FROM [dbo].[Table2] t2
INNER JOIN [dbo].[Table1] t1 on t1.favColor = t2.id;

Now Table1 would have rows deleted from it.

And finally this one?


DELETE t1
FROM [dbo].[Table1] t1
INNER JOIN [dbo].[Table2] t2 on t1.favColor = t2.id;

Table1 would have rows deleted.

This shows that in a DELETE statement with a JOIN it doesn’t matter which table comes first, the one that is listed on the DELETE line is the one that has rows deleted.

Weird Join Delete

Leave a Reply Cancel reply