SQL aggregate not yielding results

Why is this giving an empty result? I'm trying to get the min, max, and average of the set of employees who aren't supervisors.

SELECT MIN(salary), MAX(salary), AVG(salary)
FROM employee
WHERE ssn NOT IN
(SELECT superssn FROM employee)

Part of employee table

SSN       SUPERSSN
--------- ---------
888665555
333445555 888665555
987654321 888665555
987987987 987654321
123456789 333445555
999887777 987654321
666884444 333445开发者_JAVA百科555
453453453 333445555

You need to exclude nulls from the inner query. Otherwise the outer query checks against a list where one of the list values is NULL and that comparison doesn't return anything. So, the correct query should be something like.

SELECT MIN(salary), MAX(salary), AVG(salary)
FROM employee
WHERE ssn NOT IN
(SELECT superssn FROM employee where superssn IS NOT NULL)

This is why NOT EXISTS is semantically correct and more reliable

SELECT MIN(salary), MAX(salary), AVG(salary)
FROM employee E
WHERE NOT EXISTS (SELECT * FROM employee E2 WHERE E.ssn = E2.superssn)

Any NULL in the NOT IN list will always give false thanks to boolean logic. You have a NULL superssn value.