SQL NOT IN SubQuery + Null

This is something that wasn’t apparent to me for quite a while and seems to trip me up every now and again.

Say you have two tables. ‘Employees’ and ‘Payroll’.

Employees

column type
username varchar(200)
email varchar(200)

Payroll

column type
username varchar(200)
paid datetime
amount money

Not great tables, but they will get the point across.

If you wanted to select all employees who have never been paid, you would normally do a query like this:

SELECT *
FROM Employees
WHERE username NOT IN (
	SELECT username
	FROM Payroll
)

What trips up this query is if there is a NULL username in Payroll, it will never return any results. In order to make it work, you have to select all the NON NULL usernames from Payroll.

SELECT *
FROM Employees
WHERE username NOT IN (
	SELECT username
	FROM Payroll
	WHERE username IS NOT NULL
)

This behavior seems to be consistent in MySQL 5 and SQL Server 2005, I haven’t tested in Oracle.

  • Hillary

    THANK YOU, THANK YOU, THANK YOU!!! This saved me a whole lot of time!!!