When "NOT IN" in where clause gives unexpected/null result
Some time NOT IN condition in where clause gives you unexpected and null result in mysql. So to avoid this use "where column name is not null" in subquery like the bold text in example below. SELECT * FROM PatientsMigration WHERE LTRIM(RTRIM(nhinumber)) NOT IN (SELECT LTRIM(RTRIM(nhinumber)) FROM patients WHERE nhinumber IS NOT null)