Unexpected results for DELETE FROM in SQL Server-CodePudding

Something rare its happens in my code.

Check this:

if object_id('inscriptos') is not null
    drop table inscriptos;

if object_id('socios') is not null
    drop table socios;

create table socios
(
    numero int identity,
    documento char(8),
    nombre varchar(30),
    domicilio varchar(30),
    primary key (numero)
);

create table inscriptos 
(
    numerosocio int not null,
    deporte varchar(20) not null,
    matricula char(1),-- 'n' o 's'
    primary key(numerosocio, deporte),
    constraint FK_inscriptos_socio
          foreign key (numerosocio) references socios(numero)
);
GO
 
insert into socios values('23333333', 'Alberto Paredes', 'Colon 111');
insert into socios values('24444444', 'Carlos Conte', 'Sarmiento 755');
insert into socios values('25555555', 'Fabian Fuentes', 'Caseros 987');
insert into socios values('26666666', 'Hector Lopez', 'Sucre 344');

insert into inscriptos values(1, 'tenis', 's');
insert into inscriptos values(1, 'basquet', 's');
insert into inscriptos values(1, 'natacion', 's');
insert into inscriptos values(2, 'tenis', 's');
insert into inscriptos values(2, 'natacion', 's');
insert into inscriptos values(2, 'basquet', 'n');
insert into inscriptos values(2, 'futbol', 'n');
insert into inscriptos values(3, 'tenis', 's');
insert into inscriptos values(3, 'basquet', 's');
insert into inscriptos values(3, 'natacion', 'n');
insert into inscriptos values(4, 'basquet', 'n');

And here comes the error that I don't understand, I must delete all the rows where in the field "registration" = "n", as you can see there are only 4 records where the value "n" appears in the field registration. But in the following query deletes 8 RECORDS instead of deleting 4 RECORDS.

CODE

DELETE FROM inscriptos 
WHERE numerosocio IN (SELECT s.numero 
                      FROM socios AS s 
                      JOIN inscriptos AS i ON (i.numerosocio = s.numero)
                      WHERE i.matricula = 'n');

Do you know why this happens? I really can't understand it

THANKS!

CodePudding user response：

Your inner SELECT returns this:

numero
------
2
2
3
4

And so your DELETE becomes

DELETE FROM inscriptos 
WHERE numerosocio IN (2, 3, 4)

If you check what rows match that condition:

SELECT * FROM inscriptos 
WHERE numerosocio IN (2, 3, 4)

you get this result - 8 rows - and those will be deleted:

numerosocio   deporte   matricula
-----------------------------------
    2         basquet       n
    2         futbol        n
    2         natacion      s
    2         tenis         s
    3         basquet       s
    3         natacion      n
    3         tenis         s
    4         basquet       n

So what exactly do you want to delete??

How about this:

DELETE FROM inscriptos 
WHERE matricula = 'n'

This would delete those 4 rows with matricula = 'n' - why do you even need that subquery?!?!