NOT Like or <> in an NVARCHAR field is also filtering out Null - SQL Server 2008

Question

I have a column StatusCode. I want to filter out the text Complete without also filtering out NULL values.

I am using SSMS to write my query, and if I use <> or NOT LIKE, it is also filtering out the NULL values.

SELECT TOP (1) 
   dbo.WOO.WOO_StatusCode, dbo.WOO.WOO_WorkCenterID
FROM 
   dbo.WOO 
LEFT OUTER JOIN
   dbo.WKE ON dbo.WOO.WOO_RecordID = dbo.WKE.WKE_WOO_RecordID 
LEFT OUTER JOIN
   dbo.WKO ON dbo.WOO.WOO_WorkOrderID = dbo.WKO.WKO_WorkOrderID
WHERE     
   (dbo.WKO.WKO_WorkOrderID = @WorkOrder) 
   AND (NOT (dbo.WOO.WOO_StatusCode  LIKE  N'Complete'))
ORDER BY 
   dbo.WKE.WKE_LabStopTime DESC

I need the NULL's to be returned, b/c the other joined tables can still return useful information related to the record.

Essentially, how do I filter on a NVARCHAR column without omitting NULL values?

Answer 1

Then use IS NULL + OR <> N'Complete':

SELECT TOP (1) dbo.woo.woo_statuscode, 
               dbo.woo.woo_workcenterid 
FROM   dbo.woo 
       LEFT OUTER JOIN dbo.wke 
                    ON dbo.woo.woo_recordid = dbo.wke.wke_woo_recordid 
       LEFT OUTER JOIN dbo.wko 
                    ON dbo.woo.woo_workorderid = dbo.wko.wko_workorderid 
WHERE  ( dbo.wko.wko_workorderid = @WorkOrder ) 
AND (dbo.woo.woo_statuscode IS NULL OR dbo.woo.woo_statuscode <> N'Complete')
ORDER  BY dbo.wke.wke_labstoptime DESC 

Why you can't select null values with = or <>:

<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.

Which is why you can only use IS NULL/IS NOT NULL as predicates for such situations.

This behavior is not specific to SQL Server. All standards-compliant SQL dialects work the same way.

https://stackoverflow.com/a/5658472/284240

You can change this behaviour via SET ANSI NULLS OFF.