Difference between delete statements-CodePudding

DELETE a
FROM TableA a
JOIN TableB b ON a.Field1 = b.Field1 AND a.Field2 = b.Field2;

vs.

DELETE
FROM TableA
WHERE Field1 IN (
    SELECT Field1
    FROM TableB
) AND Field2 IN (
    SELECT Field2
    FROM TableB
);

CodePudding user response：

The logical conditions of the two statements are different.

The first statement will delete any row in TableA if both it's Field1 and Field2 correspond to the equivalent columns of a row in TableB.

The second statement will delete any row in TableA if the value of Field1 exists in Field1 of TableB, and the value of Field2 exists in Field2 of TableB - but that doesn't have to be in the same row.

It's easy to see the difference if you change the delete to select.

Here's an example. First, create and populate sample tables (Please save us this step in your future questions):

CREATE TABLE A 
(
    AInt int,
    AChar char(1)
);

CREATE TABLE B
(
    BInt int,
    BChar char(1)
);

INSERT INTO A (AInt, AChar) VALUES
(1, 'a'), (2, 'a'), (3, 'a'),
(1, 'b'), (2, 'b'), (3, 'b');


INSERT INTO B (BInt, BChar) VALUES
(1, 'a'),
(2, 'b'),
(3, 'c');

The statements (translated to select statements):

SELECT A.*
FROM A
JOIN B 
    ON AInt = BInt AND AChar = BChar;


SELECT *
FROM A
WHERE AInt IN (
    SELECT BInt
    FROM B
) AND AChar IN (
    SELECT BChar
    FROM B
);

Results:

AInt    AChar
1       a
2       b


AInt    AChar
1       a
2       a
3       a
1       b
2       b
3       b

And you can see a live demo on DB<>Fiddle