Just noticed something.I use scripts like this typically to remove duplicate values between tables based on a predetermined set of common fields:Delete D FROM(SELECT Firstname, Middlename, Lastname, DOB, RaceID, Height, ROW_NUMBER()OVER(ORDER BY Firstname, Middlename, Lastname, DOB, RaceID, Height) AS RowNum FROM dbo.ARR_PC)DJOIN(SELECT Firstname, Middlename, Lastname, DOB, RaceID, Height, ROW_NUMBER()OVER(ORDER BY Firstname, Middlename, Lastname, DOB, RaceID, Height) AS RowNum FROM dbo.ARR_PC)Eon D.Firstname = E.FirstnameAND D.Middlename=E.MiddlenameAND D.Lastname=E.LastnameAND D.RowNum < E.RowNumAND D.DOB=e.DOB AND d.RaceID=e.RaceIDGO
and i use scripts like this to typically find entries in one table that do not exist in another (or vice versa):SELECT * FROM douglasmni.dbo.mni t1INNER JOIN Douglas.dbo.persondata t2 ON t1.FNAME = t2.FNAME AND T1.MNAME = T2.MNAME AND T1.LNAME = T2.LNAME AND t1.dob = t2.dobORDER BY t1.LNAME
orSELECT * FROM dbo.MNI_TO_IMPORT_NO_ALIASES t1WHERE MNINO NOT IN (SELECT MNINO FROM MNI t2 WHERE t2.MNINO=t1.MNINO)
I've noticed that executing any of these will provide me with a positive set of results, but, upon closer inspection, it's actually missing entries, whether it be duplicates or entries which exist in one table but not another. In any of the fields looked at, the potential for genuine NULL values exists.For this reason, in these sorts of scripts, do i need to ALWAYS use COALESCE or wrap them line in ISNULL to get true results? And is there any benefit to one over another?Thanksjames
When do null values cause problems.
Posted - 2012-03-19 : 14:34:24
