SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

declare @Character table (id int primary key, [name] varchar(12));

insert into @Character (id, [name])
values
(1, 'tom'),
(2, 'jerry'),
(3, 'dog');

declare @NameToCharacter table (id int, nameId int, characterId int, primary key(nameId, characterId));

insert into @NameToCharacter (id, nameId, characterId)
values
(1, 1, 1),
(2, 1, 3),
(3, 1, 2),
(4, 2, 1);

declare @RequiredNames table (nameId int primary key);

insert into @RequiredNames (nameId)
values
(1),
(2);

SELECT *
FROM @Character c
WHERE EXISTS (SELECT 1
    FROM @RequiredNames rn
    LEFT JOIN @NameToCharacter nc ON nc.nameId = rn.nameId AND nc.characterId = c.id
    HAVING COUNT(*) = COUNT(nc.nameId)  -- all rows are joined
);

SELECT *
FROM @Character c
WHERE NOT EXISTS (SELECT 1
    FROM @RequiredNames rn
    WHERE NOT EXISTS (SELECT 1
        FROM @NameToCharacter nc
        WHERE nc.nameId = rn.nameId AND nc.characterId = c.id
    )
);

SELECT *
FROM @Character c
WHERE EXISTS (SELECT 1
    FROM (
      SELECT *, COUNT(*) OVER () AS cnt
      FROM @RequiredNames
    ) rn
    JOIN @NameToCharacter nc ON nc.nameId = rn.nameId AND nc.characterId = c.id
    HAVING COUNT(*) = MIN(rn.cnt)
);

id	name
1	tom

Warning: Null value is eliminated by an aggregate or other SET operation.

id	name
1	tom

id	name
1	tom