-- INIT database

CREATE TABLE samples (

  id INT IDENTITY(1, 1)

  ,department VARCHAR(1)

  ,role VARCHAR(100)

);

​

INSERT INTO samples(department, role) VALUES ('S', 'A');

INSERT INTO samples(department, role) VALUES ('S', 'B');

INSERT INTO samples(department, role) VALUES ('S', 'C');

INSERT INTO samples(department, role) VALUES ('T', 'A');

INSERT INTO samples(department, role) VALUES ('T', 'D');

INSERT INTO samples(department, role) VALUES ('T', NULL);

​

-- QUERY database

SELECT * FROM samples

WHERE

    role NOT IN

        (SELECT role FROM samples WHERE department = 'T')

    AND department = 'S'

;